using historical attributes with SCD transform on a table with a PK

I must be missing something somewhere...

I have a simple table with three fields: ID, LastName, FirstName. The ID is defined as the PK. In the table is a record of "12345, Smith, John". The incoming flat file has a record of "12345, Smith, Johnny".

In the SCD transform, the ID is the business key, and Last Name and First Name are defined as historical attributes.

During the load, the SCD transform correctly sends the data down the right path, but the insert fails with a primary key violation - as I would expect since it's trying to create a new current record.

How do I get around this problem without removing the PK

thx

Phil:

What about date fields such as an Effective Start Date and and Effective End Date to capture the valid timespan for a record

My apologies - should have read your post more closely

Phil, thanks for the info. I forgot to include the SCD indicator field in my original post.

Well, 6 hours later and here's what I have in this customers table: ID, Lastname, Firstname, SCD_Indicator, SurrogateKey where SurrogateKey is an IDENT (gasp) PK field, ID is defined as a fixed attribute in the SCD transform and Lastname/Firstname are historical.

The SCD transform is happy with this arrangement and changes to Lastname/FirstName are stored as new records with a status of 'current' (and the old record as 'expired').

But...

This table owns another table (Addresses), i.e. there is a one-to-many relationship between the customers table and the Addresses table, with the SurrogateKey implanted in the Addresses table as the foreign key to the customers table.

Storing new records in the parent table (i.e. new 'current' records) does not obviously update the child (addresses) table with the new record surrogate key ident value - so how do I update the child table to use the new parent record with the changed historical attributes

thanks again

True enough, it is more of a normalized model. And I will be tracking history in the addresses table as well.

I didn't design it, I just have to make it work. :-)

thx to all