Multiple but mutually exclusive foreign keys - is that the way to go?

Question

I have three tables: users, companies and websites. Users and companies have websites, and therefore each user entry has a foreign key in the Sites table. In addition, each company entry has a foreign key in the website table.

Now I want to include foreign keys in the website table back in the corresponding "parent" records. How should I do it? Do I have to have two foreign keys in each site entry, and one of them is always NULL? Or is there another way?

Answer 1

If we look at the model here, we will see the following:

• User linked to one website
  • The company refers to only one website.
  • A website refers to only one user or company.

The third relationship implies the existence of a user or company subject whose PRIMARY KEY must be stored somewhere.

To save it, you need to create a table in which the PRIMARY KEY of the website owner object will be stored. This table can also store attributes that are common to the user and the website.

Since this is a one-to-one relationship, website attributes can also be stored in this table.

Attributes not used by users and companies should be stored in a separate table.

To force the correct relationship, you must make a PRIMARY KEY website composite with the owner type as part of it and force the correct type into 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 ) 

Answer 2

you do not need a parent column, you can find parents with a simple selection (or join tables) in the table of users and companies. if you want to know if this is a user or a company website, I suggest using a boolean column in the table of your websites.

Answer 3

Why do you need a foreign key from a website to a user / company? The principle of non-duplication of data suggests that it would be better to scan user / company tables for the corresponding site identifier. If you really need to, you can always store a flag in the website table, which indicates whether this site entry is for the user or company, and then looks at the corresponding table.

Answer 4

First, do you really need this bi-directional connection? It is good practice to avoid this if absolutely necessary.

I understand that you want to know if the site belongs to a user or company. You can achieve this by having a simple Boolean field in the site table - [BelongsToUser]. If this is true, then you are looking at the user; if false, you are looking for a company.

Answer 5

The problem with the accepted answer (Quassnoi) is that the object relationship is wrong: the company is not a subtype of the website owner; we had companies before we had websites, and we may have companies that own websites. In addition, it seems to me that website ownership is the relationship between the website and the person or company, that is, we must have a table of relations (or two) in the diagram. This may be an acceptable approach, allowing you to keep personal ownership of the website separately from corporate ownership of the website and reduce them only if necessary, for example. via VIEW s:

 CREATE TABLE People ( person_id CHAR(9) NOT NULL UNIQUE, -- external identifier person_name VARCHAR(100) NOT NULL ); CREATE TABLE Companies ( company_id CHAR(6) NOT NULL UNIQUE, -- external identifier company_name VARCHAR(255) NOT NULL ); CREATE TABLE Websites ( url CHAR(255) NOT NULL UNIQUE ); CREATE TABLE PersonalWebsiteOwnership ( person_id CHAR(9) NOT NULL UNIQUE REFERENCES People ( person_id ), url CHAR(255) NOT NULL UNIQUE REFERENCES Websites ( url ) ); CREATE TABLE CorporateWebsiteOwnership ( company_id CHAR(6) NOT NULL UNIQUE REFERENCES Companies( company_id ), url CHAR(255) NOT NULL UNIQUE REFERENCES Websites ( url ) ); CREATE VIEW WebsiteOwnership AS SELECT url, company_name AS website_owner_name FROM CorporateWebsiteOwnership NATURAL JOIN Companies UNION SELECT url, person_name AS website_owner_name FROM PersonalWebsiteOwnership NATURAL JOIN People; 

The problem with the above is that there is no way to use database restrictions to enforce the rule that a website belongs either to a person or company, but not to both.

If we can assume that the DBMS imposes control restrictions (in accordance with the accepted answer), we can use the fact that (the person) and the company are legal entities and use the supertype table ( LegalPersons ) but still maintain the approach of the relationship table ( WebsiteOwnership ) , this time using VIEW to separate personal ownership of a website separate from corporate ownership of a website, but this time with strongly typed attributes:

 CREATE TABLE LegalPersons ( legal_person_id INT NOT NULL UNIQUE, -- internal artificial identifier legal_person_type CHAR(7) NOT NULL CHECK ( legal_person_type IN ( 'Company', 'Person' ) ), UNIQUE ( legal_person_type, legal_person_id ) ); CREATE TABLE People ( legal_person_id INT NOT NULL legal_person_type CHAR(7) NOT NULL CHECK ( legal_person_type = 'Person' ), UNIQUE ( legal_person_type, legal_person_id ), FOREIGN KEY ( legal_person_type, legal_person_id ) REFERENCES LegalPersons ( legal_person_type, legal_person_id ), person_id CHAR(9) NOT NULL UNIQUE, -- external identifier person_name VARCHAR(100) NOT NULL ); CREATE TABLE Companies ( legal_person_id INT NOT NULL legal_person_type CHAR(7) NOT NULL CHECK ( legal_person_type = 'Company' ), UNIQUE ( legal_person_type, legal_person_id ), FOREIGN KEY ( legal_person_type, legal_person_id ) REFERENCES LegalPersons ( legal_person_type, legal_person_id ), company_id CHAR(6) NOT NULL UNIQUE, -- external identifier company_name VARCHAR(255) NOT NULL ); CREATE TABLE WebsiteOwnership ( legal_person_id INT NOT NULL legal_person_type CHAR(7) NOT NULL UNIQUE ( legal_person_type, legal_person_id ), FOREIGN KEY ( legal_person_type, legal_person_id ) REFERENCES LegalPersons ( legal_person_type, legal_person_id ), url CHAR(255) NOT NULL UNIQUE REFERENCES Websites ( url ) ); CREATE VIEW CorporateWebsiteOwnership AS SELECT url, company_name FROM WebsiteOwnership NATURAL JOIN Companies; CREATE VIEW PersonalWebsiteOwnership AS SELECT url, person_name FROM WebsiteOwnership NATURAL JOIN Persons; 

We need new DBMS functions for "distributed foreign keys" ("For each row in this table, there must be exactly one row in one of these tables") and "multiple assignment" to allow adding data to tables thus limited in one SQL expression . Unfortunately, we are far from getting such opportunities!