Splitting a database with surrogate keys into physical and logical layer

Question

I am rebuilding a big warehouse database which originally used natural keys, and now I want to switch to surrogate keys.

Therefore, I am considering to split the database into a physical layer and a logical layer.

In the physical layer, every table gets basically two fields like this: (simplified case)

tblProducts:

ProductKey bigint identity (1,1) not null primary key,
ProductID nvarchar(100) not null unique

tblSales:

RowKey bigint identity (1,1) not null primary key,
ProductKey bigint not null references tblProducts(ProductKey),
DateSold date,
UnitsSold decimal,
UNIQUE (ProductKey, DateSold)

• The "Key" field is always the primary key which is also used in all foreign key relationships.
• The "ID" field is a unique nvarchar. The users will always work with the "ID" and never see the "key".

In the logical layer, the idea is to create views for every table which will "hide" the surrogate key and will allow me to work with the IDs. Even the database admins should finally work only with the views! For example:

View vwSales:

select 
    prd.ProductID,
    sls.DateSold,
    sls.UnitsSold
from tblSales sls inner join tblProducts prd on sls.ProductKey = prd.ProductKey

Now if I want to work with this view, I would need to create triggers which are handling all update/delete/insert and "translate" it to the physical layer. To create all these views is a lot of work. So:

• Is there a way to "automatically" create such views?
• It is a good approach to split the database into physical and logical layer at all?

Answer 1

While there is a way to automate the creation of DDL, I can tell you from experience this is not the way you want to go.

Yes, creating surrogate keys is best practice in a Data Warehouse (which we do). And yes, you can hide the surrogate keys from views or dimensional layers (which we also do), the automatic creation of views which are essentially the same as the underlying data (except for the surrogate key) unnecessarily burdens you with complexity that I believe you will eventually regret.

Einstein famously said "Everything should be made as simple as possible, but no simpler."

The vendor that originally built our data warehouse put in a process that creates DDL that creates a view for every physical table. Well intentioned, these views provided little value, became a maintenance problem and unnecessarily cluttered up a very well organized DW. We eventually removed all these views.

You can still get the benefits of having a different logical layer. I would recommend using views to construct your Dimensional Layer. We only publicize the "logical" dimensional layer views to business users who wish to consume DW data.

I highly recommend you research the benefits of ELT over the traditional ETL processes. It has revolutionized our DW approach, and achieved the benefits you may be seeking in a logical layer.