I have two tables:
Customer_Core with PK CustomerID that I load into Hub_Customer and Sat_Customer
Customer_Additional with PK CustomerID+Method+Number
Method is always a 2-character string (of about 20 distinct values) for which I haven’t figured out what value means what. In current implementations using this same table, they always filter on value ‘01’ but I feel like this is done solely to fix the grain difference in the easiest way. ‘01’ always exists.
Number is just an extra unique identifier, so it seems, that only one rare occasions makes a record unique. Looks like a table-scoped sequence (instead of a record-scoped sequence).
Basically, the table contains funky data, that I just have to deal with.
How would you model this table?
MAS_Customer_Additional with both Method+Number as child dependent keys, OR
(weak) Hub_Customer_Additional with a (concatenated or composite?) three-part BK and Link_Customer_Customer_Additional
I’m in favor of the first. I’ll end up with fewer tables and going towards the BDV it will be easier to apply a business rule (where I most probably will be doing some filtering similar to the current implementation) to end up with a satellite with the same grain as Customer.
I’m going to order your book tonight (I really am!), hoping to find the answer in there as well. In the meantime, do you happen to have a blog post somewhere that describes the difference between MSat and a Satellite with a dependent-child key (and isn’t there a nice abbreviation for that as well?)? I think it will be similar to the explanation in this blog post, right?
Is the dbtvault implementation of an MSat what you call a DCK Sat? (also, kind of funny, the use the term Child-Dependent Key, not Dependent Child Key)
Sweet, looking forward to the book then! It makes me wonder, what the dbtvault team has used for inspiration (or in a more literal sense, where they found the SQL or to what degree they came up with the SQL themselves) for their implementation of all patterns.
Really appreciate your swift and elaborate responses