How do we make a table relationship when one index could be from two different tables?

Question

I have table called addresses that stores N:N sub-information from those two tables people and companies.

So I created another table called address_links that have the fields address_id and contact_id so that contact id could come from a people or from a companies record.

So I need another field that by the creation of the record, points to the correct table. But what can I do to automatize that interpretation when I make a query that shows the name of the owners of that addresses in a query list? I tried IF in order to case that 3rd field by selecting the table but it did not worked.

Explain Note: Some address may be house or workplace for more than one person.

Answer 1

If you need to link to different tables, then normally you would want to have a separate link table for each. This allows the database to enforce referential integrity and eliminates the need for special if statements, it also makes the database easier to understand if another developer looks at it without having to understand special implementation details.

Example Tables (columns):

 Addresses (AddressId, Address, City, State, Zip, ...)
 Persons (PersonId, FirstName, ...)
 Companies (CompanyId, Name, ...)

 AddressPersonLinks (AddressId, PersonId)
 AddressCompanyLinks (AddressId, CompanyId)