SQL DB Architecturing: How to reuse one child table for two parent tables.

The question raised long time ago:
How to reuse one child table for two different parent tables?

The Classic example is address table. Would say in our database we handle companies and individuals separately and we have to keep their addresses in outside reference table.

CREATE TABLE Company(   Company_ID INT IDENTITY(1,1),   Company_Name VARCHAR(100),   CONSTRAINT PK_Company PRIMARY KEY CLUSTERED (Company_ID ASC) ) GO CREATE TABLE Person(   Person_ID INT IDENTITY(1,1),   Person_Name VARCHAR(100),   CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (Person_ID ASC) ) GO CREATE TABLE Address(   Address_ID INT IDENTITY(1,1),   Address VARCHAR(100),   Address_Type VARCHAR(20),   CONSTRAINT PK_Address PRIMARY KEY CLUSTERED (Address_ID ASC) ) GO

* For the sake of simplicity I have only ID and a name


What choices we have to establish relationship from Company and Person to an Address?
1. First option is to create separate address tables:

2. Second option is to create reference tables:

I really did not like both options and wanted to do something like this:

The problem with that is that I can't build a foreign key that will be related to both parent tables at once.

At firs, I tried to use aggregated foreign key against a view that would be a combination of "Company" and "Person" tables.
However I badly failed on that.
I tried unionize these tables, built a CTE query, query with FULL OUTER JOIN - nothing helped I couldn't build a clustered index over that view.

So, I decided to go other way.
I've created TWO reference ID columns in the address table and added Check Constraint to prevent these two values being NULL or NOT NULL:

ALTER TABLE Address ADD Company_ID INT  GO  ALTER TABLE Address ADD Person_ID INT  GO  ALTER TABLE Address ADD CONSTRAINT CHK_Address CHECK (NOT (Company_ID is null and Person_ID is null)          and NOT (Company_ID is not null and Person_ID is not null))

Then I've created Foreign Key relationships to parent table:

ALTER TABLE Address ADD CONSTRAINT FK_Address_Company        FOREIGN KEY (Company_ID) REFERENCES Company (Company_ID)               ON UPDATE NO ACTION ON DELETE NO ACTION GO ALTER TABLE Address ADD CONSTRAINT FK_Address_Person        FOREIGN KEY (Person_ID) REFERENCES Person (Person_ID)               ON UPDATE NO ACTION ON DELETE NO ACTION GO

Then I insert The data

INSERT INTO Company (Company_Name) VALUES ('Microsoft'),('Oracle'),('Dell'),('Idera'),('HortonWorks')   GO INSERT INTO Person (Person_Name) VALUES ('Bill Gates'),('John Doe'),('Joe the Plumber')   GO INSERT INTO Address(Company_ID,Address, Address_Type) VALUES (1,'33 Oak St.',('Headquarter'),        (2,'1235 Limpopo Ave.',('Main Office'),        (3,'1547 Caiman Isl.',('Mailing'),        (4,'WWW.IDERA.COM',('Web'),        (5,'127.0.0.1',('Home') GO INSERT INTO Address(Person_ID,Address, Address_Type)   VALUES (1,'Hawaii',('Vacational'),        (2,'1507 Malholland Dr.',('Second Home'),        (3,'Corner of 5-th Ave. and 7-th St.',('Sleeping Place') GO

And here is a query to extract the data:

SELECT       CASE WHEN c.Company_ID IS NULL THEN 'Person' ELSE 'Company' END as Client_Type,       a.Address as Client_Address, a.Address_Type   FROM Address as a LEFT JOIN Company as c on c.Company_ID = a.Company_ID LEFT JOIN Person as p on p.Person_ID = a.Person_ID   GO

Below are results of the query:

Now will try to justify if my solution is bulletproof enough and can keep database integrity.
Will run following queries trying to violate all constraints:

GO -- Error 1: Trying to insert address without corresponding Company or Person INSERT INTO Address(Address, Address_Type) VALUES ('4905 Ocean Pkwy.', 'Office') GO -- Error 2: Trying to insert address for two parents at onceINSERT INTO Address(Company_ID, Person_ID, Address, Address_Type) VALUES (1,1,'4905 Ocean Pkwy.', 'Office') GO -- Error 3: Trying to insert address for not existing CompanyINSERT INTO Address(Company_ID, Address, Address_Type) VALUES (100,'4905 Ocean Pkwy.', 'Office') GO -- Error 4: Trying to insert address for not existing Person INSERT INTO Address(Person_ID, Address, Address_Type) VALUES (100,'4905 Ocean Pkwy.', 'Office') GO -- Error 5: Trying to Delete a Company which has linked address DELETE FROM Company WHERE Company_ID = 5 GO -- Error 6: Trying to Delete a Person which has linked address DELETE FROM Person WHERE Person_ID = 2 GO -- Error 7: Trying to Drop Company table DROP TABLE Company GO -- Error 8: Trying to Drop Person table DROP TABLE Person GO -- Here is an example of successful deletes: DELETE FROM Address WHERE Company_ID = 3 or Person_ID = 3 GO DELETE FROM Company WHERE Company_ID = 3 GO DELETE FROM Person WHERE Person_ID = 3 GO

Here are results of that query:


Congratulations to myself! Everything work as expected.

Disclaimer:
That solution has never been implemented into production. So, please be careful to use it in your system.

I know the solution looks ugly, but it works. Please criticize it. Than together we can define areas of it's proper usage.

Thanks.