Hi

I have a an entity A, entity B and Entity C.A has a Many to Many relationship with B and C. I created associate tables rel_a_b and rel_a_c.
A,B and C are all Slow changing dimensions. I have surrogate Keys for each of these tables that gets changed whenever a description (or any scd column chaages). I have the SKs as the composite key in rel_a_b and rel_a_c. rel_a_b and rel_a_c has some attributes that can change like accociation_type, prorate, etc. I can include a SK here to take care of this and move the SKs out of PK or I can include the current_date in the PK to track that. The question I have here is if there is small change in (say desc) in entitiy A, then I have repopulate rel_a_b and rel_a_c with the new SKs so that the description is latest when queried from dimension C or B perspective. Now rel_a_b and rel_a_c could be lot of rows. Is there a way I can avoid populating these associate tbales with SKs whenever there is small chnage and populate that table only when the relationship chnages.

Any help is greatly appreciated.

Thanks

why does your SK need to change whenever an SCD column changes?

SCD is Kimball dimensional terminology but this doesn't sound much like a regular star or snowflake schema.

In the association tables why not use the business key from the A,B,C entities?

Thanks for the response. If I don't change the SK then I have to include the start date in the key. currently I have just the SK as the PK in the dimension tables, so that when a change occurs I change the SK and insert another row (type 2) with the new values.

Thanks

Thanks for the response,

Actually entity A is connected to fact table with other dimensions (not mentioned for clarity).

I am having SK in entity A, B and C to track changes (type 2). I do not have business keys in the Primary key and so cannot have the business keys as FKs in the association tables. Am I missing something here?

Thanks for your help in advance!

I was assuming that your database would be Normal Form in which case you could create new tables with the business keys.

If you are restricting yourself to the Kimball modelling straitjacket then Google for "helper tables" to see what passes for a solution in your world [Sorry - I find it difficult even to discuss that stuff without feeling angry and ill ].