Good practise to split data over multiple databases? [closed]

Question

I am designing a database for use in an information system. It will have data on customers, employees and items. Needless to say, the customers section alone has about a dozen tables.

I would like to know if it's bad practise to split this data over multiple DBs and then just refer amongst them, or if keeping them all in the same DB would be better.

So, rather having a single DB (e.g. Company_DB) with two dozen tables (e.g CUS_DETAILS, EMP_DETAILS, etc), would it be better to have two DBs (Company_Cus, Company_Emp) with the CUS_DETAILS in one and EMP_DETAILS in the other?

Regards.

Answer 1

"Good practice" is one of those phrases that allows us to make our prejudices sound more important than they really are...

In general, you want to store things together that belong together. If you have a single information system, that runs everything for a business, and the entities you manage are related to each other - customers have orders, orders have sales people and products - you could argue they belong together. If, on the other hand, you notice that you have "islands" of tables that don't really link to any other tables, perhaps they don't belong together.

From a maintainability point of view, managing more databases means more back-ups, more maintenance routines, and more security profiles to manage.

From a readability point of view, hunting around different databases to find the table you're interested in is probably a bad thing - for instance, where there is a many-to-many join table between "customers" and "employees" - e.g. sales team - where does that table live? In the "customer" database, or "employee"?

From a reliability point of view, I'm not sure if you can enforce foreign key constraints across databases.

I can't really think of any benefits of doing this - so I would say it's a bad idea.

Answer 2

I think it's better to have it all in one database, rather than having data scattered throughout several databases.

Personally I think the only reason to separate information in different databases would be to have systems or sub-systems, totally different from each other, on a single platform.

The amount of data will be the same, regardless of how many databases you use.