Multiple yet mutually exclusive foreign keys - is this the way to go?

If we look into the model here, we will see the following: A user is related to exactly one website A company is related to exactly one website A website is related to exactly one user or company The third relation implies existence of a "user or company" entity whose PRIMARY KEY should be stored somewhere To store it you need to create a table that would store a PRIMARY KEY of a website owner entity. This table can also store attributes common for a user and a website Since it's a one-to-one relation, website attributes can be stored in this table too The attributes not shared by users and companies should be stored in the separate table To force the correct relationships, you need to make the PRIMARY KEY of the website composite with owner type as a part of it, and force the correct type in the child tables with a CHECK constraint: CREATE TABLE website_owner ( type INT NOT NULL, id INT NOT NULL, website_attributes, common_attributes, CHECK (type IN (1, 2)) -- 1 for user, 2 for company PRIMARY KEY (type, id) ) CREATE TABLE user ( type INT NOT NULL, id INT NOT NULL PRIMARY KEY, user_attributes, CHECK (type = 1), FOREIGN KEY (type, id) REFERENCES website_owner ) CREATE TABLE company ( type INT NOT NULL, id INT NOT NULL PRIMARY KEY, company_attributes, CHECK (type = 2), FOREIGN KEY (type, id) REFERENCES website_owner ).

If we look into the model here, we will see the following: A user is related to exactly one website A company is related to exactly one website A website is related to exactly one user or company The third relation implies existence of a "user or company" entity whose PRIMARY KEY should be stored somewhere. To store it you need to create a table that would store a PRIMARY KEY of a website owner entity. This table can also store attributes common for a user and a website.

Since it's a one-to-one relation, website attributes can be stored in this table too. The attributes not shared by users and companies should be stored in the separate table. To force the correct relationships, you need to make the PRIMARY KEY of the website composite with owner type as a part of it, and force the correct type in the child tables with a CHECK constraint: CREATE TABLE website_owner ( type INT NOT NULL, id INT NOT NULL, website_attributes, common_attributes, CHECK (type IN (1, 2)) -- 1 for user, 2 for company PRIMARY KEY (type, id) ) CREATE TABLE user ( type INT NOT NULL, id INT NOT NULL PRIMARY KEY, user_attributes, CHECK (type = 1), FOREIGN KEY (type, id) REFERENCES website_owner ) CREATE TABLE company ( type INT NOT NULL, id INT NOT NULL PRIMARY KEY, company_attributes, CHECK (type = 2), FOREIGN KEY (type, id) REFERENCES website_owner ).

You don’t need a parent column, you can lookup the parents with a simple select (or join the tables) on the users and companies table. If you want to know if this is a user or a company website I suggest using a boolean column in your websites table.

1 - pretty well exactly what I said, so I clearly think you're right! – Dominic Rodger Sep 29 '09 at 15:21.

The principle of not duplicating data would suggest it might be better to scan the user/company tables for a matching website id. If you really need to you could always store a flag in the website table that denotes whether a given website record is for a user or a company, and then scan the appropriate table.

It is a good practice to avoid it unless absolutely needed. I understand it that you wish to know whether the site belongs to a user or to a company. You can achieve that by having a simple boolean field in the Website table - BelongsToUser.

If true, then you look up a user, if false - you look up a company.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.