I was reading this question:
I was trying to standardize and modularize some functions (Email
Management Module, CMS Module & etc) by implementing a 3-tier
architecture concept where each module would have its own independent
module database. So that in the future all we’d need to do is just
code a presentation layer, reuse the BLL layer, DAL Layer and
database.
My follow-up question is whether it is a good idea to place all the database tables from each module into the same database, or whether they should be separated into entirely separate databases? I am using PostgreSQL. My worries are:
- Problems with running data analysis if data is in many different databases
- Problems pinning down database performance issues if we use the same database for all modules
- In ability to join tables across modules if we some time in the future discover that our modularization is flawed for creating a certain feature
0
If you want to combine data from different modules in any way, then you really want a single database.
The proper way to compartmentalize things within one database is to use schemas:
- Schemas have separate namespaces, so using a schema for each module ensures that there will be no clashes between modules.
email.table1will never clash withcms.table1. - Permissions can be set at a schema level, so the email module could be only allowed to access the email section, for example.
I don’t think that having a single database will create any problems with regard to module installation/removal. It is really no big deal to add and remove tables/schemas in a database.
As for performance, if you have performance problems you generally have to drill down and see what queries are causing the problems anyway. I don’t think it will make much of a difference whether it is in one database or several.
If done correctly, separating business concerns into different databases (or at least different schemas) is a virtue.
Please see Martin Fowler’s description of the CQRS Pattern:
As our needs become more sophisticated we steadily move away from [treating an information system like a CRUD datastore]… The change that CQRS introduces is to split that conceptual model into separate models for update and display… There’s room for considerable variation here. The in-memory models may share the same database, in which case the database acts as the communication between the two models. However they may also use separate databases, effectively making the query-side’s database by a real-time ReportingDatabase. In this case there needs to be some communication mechanism between the two models or their databases.
And NServiceBus Architectural Principles:
Command Query Separation
A solution that avoids this problem separates commands and queries at the system-level, even above that of client and server. In this solution there are two “services” that span both client and server – one in charge of commands (create, update, delete), the other in charge of queries (read). These services communicate only via messages – one cannot access the database of the other…
And Command and Query Responsibility Segregation (CQRS)
Command and Query Responsibility Segregation
Most applications reads data more frequently than they write data. Based on that statement, it would be a good idea to make a solution where easily you can add more databases to read from, right? So what if we set up a database dedicated just for reading? Even better; what if we design the database in a way so it’s faster to read from it? If you design your applications based on the patterns described in CQRS architecture, you will have a solution that is scalable and fast at reading data.
- Yes, that could be a problem if you want to analyze multiple databases at once. You could migrate and merge all of the data into a single analysis database and do your analysis there. That means you’ll have to design a schema that will avoid any name clashes between tables and other objects, and a process to do the migration but it can be done.
- Yes, that could also be a problem.
- Yes, that could also be a proble, although each module could also have a data-access API to allow others access to data. This would make it difficult to have queries that join across databases, but it would be possible. Database links are also possible in Oracle (they allow a query run in one database direct access to objects in another), but I can’t speak for other vendors’ products.
And then then there’s extra maintenance issues of having separate databases for each module, such as ensuring that they are all set up correctly, all of them are being backed up correctly, etc…
On the other hand, if you really do plan to have scenarios where there are many modules and they are not all deployed with each other, having separate databases might make sense when setting up new deployments. And adding new modules might be easier if you can ensure that the installation won’t have to touch any existing databases.