this kind of argument has been discussed many times but I can't find a precise answer.
I have two dimension tables, say Customer and Address, that i want to model after scd type 2 pattern, with valid_from and valid_to fields, and address having a fk to customer.
The question is: when Customer changes, creating a new record and a new synthetic primary key, do I need to create a new record for the linked addresses or should I model those data differently? In my case I don't think I can put everything in the same table because the real case is a little more complex.
Another solution is to create an intermediary table, called CustomerAddress that would contain only primary key mappings, so for example if a customer changes I would create also a new record in CustomerAddress copying the old address pk and putting the new customer pk. I don't really like this solution though because it is probably hard to maintain (and in my case expose to the sqlalchemy orm)
thanks
Asked by DRC
(111 rep)
Mar 5, 2023, 08:53 PM