Using core/model database with small changes on different installations for customization without duplicating code

Question

As an example core database looks like this:

Person  - Id, FirstName, LastName
Address - Id, Street, ZipCode

This model is used on the majority of the installations but for a couple of installations I want to add customized columns, let's say the column State to Address for Customer X and BirthDate to Person for Customer Y.

Now, I don't want to add these columns to the core database because the State column is only used by our US customers and the BirthDate is only used by our European customers were BirthDate is obligatory (NOT NULL) and would therefore need a dummy value for all the other customers.

I was checking in GitHub about having several branches - Core and US-Customer - but that would mean that the whole Core solution would be copied over to the US-Customer branch but when the Core branch is changed it would not update the US-Customer branch i.e. having to maintain basically two repositories.

Is it possible to have this sort of customization either in SQL Data Tools or in GitHub were the customized solutions (e.g. US-Customer) would only have the absolutely necessary changes and without SQL Data Tools throwing errors in Visual Studio about missing tables or any other error?

Example:

Core branch

Person  - Id, FirstName, LastName
Address - Id, Street, ZipCode

US-Customer branch

ALTER TABLE Address ADD State CHAR(2) NOT NULL

EU-Customer branch

ALTER TABLE Person ADD BirthDate DATE NOT NULL

Answer 1

There is no very easy way of implementing so. If you wish to do that completely in SSDT way, then you need to create several projects:

• ProjectCore --> will have all shared objects. It shouldn't have references to the projects below;
• ProjectUS --> will have all the objects for US and reference ProjectCore as The same database;
• ProjectEU --> will have all the objects for EU and reference ProjectCore as The same database.

Both ProjectUS and ProjectEU will have their own publish profiles. You'll have put full definition of different objects there, not just ALTER statements.