1

I bet this may be a duplicate and I'm not forming the question correctly, so feel free to point me in the right direction.

Background

We have a SQL Server with some legacy data. They're all addresses, but stored in separate tables (I know, ugh.) so, we have a tblDefenseCounsel, tblPlaintiffCounsel, tblVendor, tblInsurer, etc., all with addresses. I don't have the ability to change that currently unfortunately.

We are creating a new table for a feature that has to store a contact -- except the contact can be from any of those locations.

Goal

Organize this generic contact table in a way that will cause us the least amount of agony.

Choices

As I see it, we have the following choices:

  • Create a Table such as | TableReferenceID | ContactID | with a lookup table that stores the database tables its from
  • Create a table such as | TableName | ContactID |, which stores the table name in text
  • Some third option?

Question

What is the proper way to store data of this nature, or what are the valid options? Are there additional options besides those I've listed?

3
  • What is a "contact" in this context? Just a name and phone number? Commented Apr 23, 2014 at 17:55
  • @MarkWilkinson in this case it's a name, address, and phone number, and all generally for businesses. Commented Apr 23, 2014 at 19:23
  • In that case, similar to what Matt says, I would think you would need to create a table to store the contact info, and then a relationship table for each table that could have a contact. For example, tblDefenseCounsel would have a corresponding relationship table called tblDefenseCounselContact. That table would have 2 columns: tblDefenseCounselID and ContactID. This is all assuming you can't touch the existing schema. Commented Apr 23, 2014 at 19:42

1 Answer 1

1

Have you considered modeling where contact info exists independent of type. Could a contact be used for defense counsel and latter for vendor?

Assuming you are trying to fix the data and hide it from the program might i suggest the following:

  1. Create a generic contact table and import all contacts here
  2. Create bridge tables like tblDefenseCounselContacts with (oldID,ContactID)
  3. View to replace tblDefenseCounsel and triggers as needed.
  4. repeat for other tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.