Is it good practice to split data across multiple databases?
I am creating a database for use in an information system. He will have customer, employee and item data. Needless to say, there are only about a dozen tables in the customers section.
I would like to know if it is bad practice to split this data across multiple DBs and then just reference them, or if keeping them all in one DB would be better.
So, rather than have one database (for example, Company_DB ) with two dozen tables (eg CUS_DETAILS
, EMP_DETAILS
etc.), it would be better to have two databases ( Company_Cus, Company_Emp ) with CUS_DETAILS
one and EMP_DETAILS
the other?
Sincerely.
source to share
Good practice is one of those phrases that allows us to make our prejudices more important than they really are ...
In general, you want to keep things together that belong together. If you have a single information system that runs everything for the business, and the entities you manage are connected to each other - customers have orders, orders have salespeople, and products - you can argue that they belong to each other. If, on the other hand, you notice that you have "islands" of tables that are not really related to other tables, they probably do not belong together.
In terms of serviceability, managing large databases means more backups, more maintenance procedures, and more security profiles to manage.
From a readability standpoint, searching around different databases to find the table you are interested in is probably bad - for example, when there is a many-to-many junction table between "customers" and "employees" - such as a sales team - where does this table live in a "customer" or "employee" database?
From a reliability standpoint, I'm not sure if you can enforce foreign key constraints on databases.
I cannot think of any benefits of this, so I would say it is a bad idea.
source to share
I think it's better to have it all in one database, rather than scatter the data across multiple databases.
Personally, I believe that the only reason for separating information in different databases would be to have systems or subsystems that are completely different from each other on the same platform.
The amount of data will be the same no matter how many databases you use.
source to share
Are you storing databases on one disk? If so, splitting it across different databases won't give you any improvement. Links between multiple databases are slower than multiple tables in one database.
However, a single db will run faster and easier: only one database to protect, only one database connection to manage in the application, etc.
The reason for the existence of the database is to keep a group of related objects together so that we can manage them accordingly (otherwise we would just create the procs and views tables directly without linking them to the database, and each table would have its own file, statistics etc.).
If you think putting tables in the same database is going to be a mess, maybe you need a better pattern for naming objects?
Bottom line: It seems like a very bad practice to me as it doesn't provide any advantages and many disadvantages.
source to share