Database design to merge many different product databases into a single database

Option 1, using separate schema, dbo, product1, product2, product3, product4, seems a good choice. Move common things to the dbo schema.

Any migration to one database in future?

Thank you, in the future for now I was advised that no requirement to merge data from previous installations at this point in time however, the requirement might change after a few months. 🙂

Yeah, I'd go with option 1.

However, are these different databases currently on different development and release cycles? If so, putting them all into a single database might prove to be a bit of a horror show. I've seen it be a major problem in the past. Different teams make different changes on varying schedules. You have two options. You'll have to branch the releases (which means different databases in development) and then merge them through QA or some other pre-production stage, and then deal with all the discrepancies as the varying changes to the database have to get resolved. The other option is to get the development teams to coordinate their releases and communicate well with each other. Good luck.

Yes, they are different databases with their own release cycle. However, I impressed that I will perform database modifications required to insure performance, organization and I hope a better database design. So, there needs to be more control over changes to validate if a change on one system affects other systems at least that is the idea so, your latter idea of better coordination between development teams will definitely be the key here.

I'm assuming the goal here is to consolidate multiple OLTP databases into one OLTP database, and each existing line of business application will (at least for now) continue it's own life cycle separately, although they mare share some common tables. I'd suggest going with one schema for each application with a separate schema for common shared tables. Static or periodically reloaded reference tables (lookups, zip codes, etc.) should go in one schema, perhaps called [Reference], and shared transaction tables (Customer, PurchaseOrders, etc.) should go in it's own schema called something like [Customer] or [Sales].

Also, for the shared schemas, you can implement application specific view(s) for each table, thus creating an abstraction layer to accomodate special naming conventions, filtering, or additional columns.

Be prepared for a lot of friction. It's actually difficult to coordinate releases of this type.

Yes essentially we will be consolidating these databases to produce one product or suite. Yes, these are good points as well it seems like I have to brush up on Schema definitions to be sure the objects are placed correctly.

I certainly hope not but, I think these releases will have to consider design in more detail to assure a change for one product does not in adversely impact another. You are correct it will not be easy.

If you're going down this road, make sure you set up very good automated testing. I'd suggest getting continuous integration set up. That requires doing your development out of source control. But all this will get the process in hand and will help reduce or mitigate the friction.

Yes good point. We will see about doing this we already purchased some licenses for Redgate SQL Source Control and hope that will help track our changes by who and when with comments.

Ha! Well that's good news. I wasn't even going to bring up how to get your database into source control.

😎 Well, I was being proactive as I knew someone was about to suggest this at some point when source control and databases was introduced.

Merging the databases is a tiny part of what needs to be done.

IMHO the focus should be on the business problem, not on a piece of the technical solution. If the computer team are doing this work 'for the good of the business' it is unlikely they will be thanked by anyone. Certainly if this happened where I work I would be one of those not doing the thanking.

What you are outlining is a major redevelopment to a critical business system, and as such it needs sponsorship from the business and a proper project structure. The business requirements need to be clearly stated, so that all other work can focus on meeting these.

With the level of redevelopment you are talking about then any solution needs to evaluate software packages and cloud services before looking at technical issues such as merging databases. When the optimal business solution becomes obvious then it is time to look at the technical details. The details of the business solution will shape the database design. Questions about if and how to merge databases will be driven down the stack by the business solution, not driven up the stack by a decision within the technical team.

I do agree with your overall point however, I am playing the DBA role in this project. So, I will leave these "business" decisions, directions, and requirements to the Product Managers and Project Managers of this project. As far as my DBA role, I received these requirements from the project owners and in so doing I advised the team that database redesign will be performed in order to incorporate the various product lines into one database and reorganized for better database performance and so forth, and to have this as part of the project planning. So, here goes at this point.