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.
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
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.
Organize this generic contact table in a way that will cause us the least amount of agony.
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?
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?