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.
- Single database (with secure vault possibly)
- 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?