merging two duplicate contacts/ColdFusion

Question

having to do with data integrity - I maintain a coldfusion database at a small shop that keeps addresses of different contacts. These contacts sometimes contain notes in them.

When you are merging two duplicate contacts, one may be created in 2002 and one in 2008. If the contact in 2002 has notes prior to 2008, my question would be does it matter if you merge these contacts and keep the 2008 contact's ID number? Would that affect the data integrity or create any sort of issues with the notes earlier than 2008?

I hope I've accurately described my scenario, as I am not familiar with the proper technical terms.

I really appreciate the help sir!

Answer 1

It sounds like you have two tables - contacts and notes. The notes table has a foreign key back to the contacts table to record which contact a note belongs to.

So, imagine two Contacts - Bill (primary key 1, created in 2002) and William (primary key 2, created in 2008).

Imagine one Note with a foreign key 1 (ie this note belongs to Bill).

If you merge Bill and William, and only keep the William record, then you would need to update the foreign key from 1 (Bill - deleted) to 2 (William) on the note or it will not display on William's record.

(If you add further details about your table structure we can probably help more.)

Answer 2

I will say that the fact that the app is ColdFusion is pretty well irrelevant to your problem.

It seems like some of what you're asking depends on your business requirements. Do you need to retain older notes?

As other folks are saying, it depends in large part on your table structure. If, as I suspect, you've got just one table that has a NOTES column in it, you'll need to figure out how to concatenate the values in multiple rows that all refer to the same person.