Sql-server – Table with multiple lookups

relational-theorysql server

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?

Best Answer

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.
Related Question