Creating multiple databases or one

Question

Our company has 4 business entities, so I have created 4 different databases for each company to be used for Human Resource service. Let's call that as a Group, Company1, Company2, and Company3. Even though they are all different databases, tables and store procedures(SP) are almost same except Group. Group is a database that gets summarized data from each company. I did create this way because of security purpose, but now it is kind of hard to manage. When I change a SP for company1, then I have to do same thing for company2 and company3. All 4 databases have same a employee master table,so when someone is added to companies, then that person has to be added to the employee table of Group DB by trigger or SP which I don't have to if they are in one table of a database. by the way, I try not to use view at Group DB. I have to keep adding SP, trigger, and job into the database in order to communicate Group DB and other three company DBs.

Now I have to create two more companies, so I have to consider this matter whether I have to keep creating same tables and SP again as a separate database or not.

What is your opinion for creating this types of database? Would you prefer one DB for all companies or separated 4 databases? Please, I'd like to share your opinion. Thanks.

Answer 1

You should put it all into one database. Reasons for that are numerous, but you already bumped into one - shared employees table...

Security can be tailored so the specific groups of people can access specific tables, and so on.

Well, the road is long, take that first step :)

Answer 2

Put all the common stuff, including sps, in one db, then have a db for whatever is specific to each company. You should never have to update anything in more than one place.

Answer 3

You have some very strong cases for using a single database to store all the data. From your description it sounds like you could design it in a way which would make not only much of your deployment work easier, but also simplify any aggregate reporting which would happen across the group.

You'll have to weigh those benefits against your information security requirements. I know in certain environments that separate databases are a necessity for security, dictated by law or company policy. If you don't have any of those strict security requirements, I recommend the single database approach.