Can a business key in SCD have a duplicate data. I'm using member_id as a business key where it has multiple records with member_id example

member_id  relationship

10              child

10               father

Member id is the only column I can use as a business key. So, with duplicate member_id's in it will that be a problem?

Thanks,

Hi sree,

Yes, it is going to be as you cannot reliably identify a record, looks like the composite key may work here - member_id  + relationship = father may work

Thanks for your valuable reply.

How do I select Composite key in SCD?

Good question, you will need to roll your won surrogate/artificial PK - add a new column that will assign it off the combination of these two.

Hi

Member_id does seem to be a valid business key for a dimension and can be used without a problem.

However an attribute on a dimension can only have a single value at any one time, if this value changes over time then we call it a slowly changing dimension. Your example however lists two (or more) simultaneous relationships and would be better modeled as as fact.

X has a relationship with Y

Can you please elaborate a little more on this.