question

If you can change your model, you can push Account.AccountNumber into the other tables so you have Account.AccountNumber, Address.AccountNumber and CreditCard.AccountNumber This way, RowIDPrimary can change all it needs to and you use AccountNumber to tie the new CreditCard to the existing Account. If you want to leave the model as it is, you'll have to write a new Account row for each additional CreditCard you import. You would then tie the new CreditCard to the existing Account using the newly written Account.AccountNumber

The relationships you provided already allow multiple Address and CreditCard rows per RowIDPrimary which ties these together with Account. If you allows multiple Account rows for each RowIDPrimary, how to you currently tie addresses and credit cards to accounts - just assign each address and credit card to each related account? same with credit cards?

Ok for this example: You have 1 row with RowIDPrimary of 1. There is an address, Account, Credit Card all with same RowIDPrimary. If in a future import you had another credit card come down for the customer with ID 1 how would this link back? I'm planning on using a MERGE statement to check my tables, so it would see oh customer with ID of 1 is already there, but he has another credit card so add a new credit card creating a new ID i can't get my head around how i can still have referential integrity in this situation.

hi ken sorry for delayed response. I am able to change my model. Can you elaborate on your method? Would i need to create a composite primary key or more foreign keys etc.?

guess we're both delayed :) since i'm not sure what the import files would look like, I'll just speculate that each table in the model is represented by a corresponding flat-file and that, currently, you could get a new credit card row in the flat-file without the accompanying row in the account file. i'm thinking that when the credit card flat-file is populated with an additional credit card sometime in the future, the account file could also be populated with account data so the new credit card can be tied to the existing account when it is imported using rows.rownumber - just like the first time an account/credit card combination arrived. alternatively, you would add an account number column to the credit card table so account and credit card could be directly related rather than via rows.rownumber. When the additional credit card arrived in the future, it would have the account number with it right inside the credit card flat-file and you wouldn't need the corresponding row in the account flat-file.