What are criteria for enterprise to have multiple databases (for new project)?

Question

We are working on new enterprise project (multiple activities - online ordering, offline ordering, b2b ordering, accounting processing, secure payment processing, analysis and dynamic pricing and so on) without legacy burden and we are considering 2 scenarios.

1. Single database (with secure vault possibly)
2. Multiple databases - e.g. there can be a fast database for online ordering and web page, another database for order fulfilling and some other databases for other web pages. All those databases are synchronized by specialised API which runs periodically by daemons or which happens online on some business event. But synchronization is quite tricky, sometimes with long execution times and multiple points for failure. Although multiple databases can be a good design, practical implementation is quite involved.

So, what are criteria when enterprise databases should be separated?

I know only one sure criterion: an old transaction should be retired on data warehouse where analysis is being done. But, e.g. SAP HANA discourage separation and suggest one in-memory database. However, the smooth distribution of work among teams is a plus for the development with multiple databases. Maybe, separation creates more reliability, e.g. web database can be simple and can be always running, but some other database can be allowed to have some moderate failures. Maybe, the answer is dependent on the database product. I.e. maybe if SAP HANA is used then one database for enterprise is the right answer, but if Postgres is used then it is advised to use it only for back-office processing but one is required to use MySQL/MariaDB for web activities?

Answer 1

It can happen to require multiple databases, but generally this happens out of necessity, and not for design or performance reasons. The reasoning is that the usage of a database and its advantages/disadvantages generally aren't going to be a significant enough reason to merit using more than one database.

Though I have written an application which potentially had two separate databases. The necessity came from the fact that our company sells and supports a framework which is heavily reliant on the database. Rather than necessarily appending additional tables to the same database, it was decided to accept two separate database connections (web application referred to these connections purely by name, allowing the details of the connection to be set at installation time). The first would be the framework in which the data is drawn (read-only database) and the second would be the database necessary for the application to work properly (write database). Done in this way, the two databases could potentially be one and the same, allowing maximum flexibility according to what the client prefers.

But you'll notice that done this way, it isn't an issue of performance but of necessity. If you feel that you may use multiple databases in the future, there is nothing preventing you from doing as I did and use multiple database connections which may point to the same database and change it later. In your case, it would seem that there may be a logical basis for separating data used for analysis from the data used with high frequency. If done properly, this too can represent the same database or multiple databases, as deemed necessary in the future, with little to no hassle on your part.

Though in general, you should avoid using multiple databases. If you do so, you should have a good rationale behind it, because otherwise you're needlessly complicating your application.

Answer 2

We are working on new enterprise project (multiple activities - online ordering, offline ordering, b2b ordering, accounting processing, secure payment processing, analysis and dynamic pricing and so on)

If you have multiple activities, each with their own separate responsibilities, it's likely that you will want several applications. Applications should not share databases, so you should probably have more than one database.

In your example off the top of my head, I would split it as follows

• Ordering of various kinds
• Secure Payment
• accounting processing??
• analysis and dynamic pricing??

Clearly Ordering and Payment can be separated out into different applications. Indeed you will probably want more than one payment application. All Ordering needs to know is whether the payment has been made for an order. There is no need to share a database and it reduces your security footprint if the Order database does not contain any payment info.

Answer 3

One reason is if parts of an enterprise may end up being sold off as distinct companies.

This happened with me, with a large database ending up being split into four.

In fact initially I made two databases, but was instructed to merge them, the split happened later. It all added up to a lot of extra work, merging and demerging.

So think ahead, and decide if there is a reasonable possibility of an enterprise being split up.