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

Question

I have three tables: Users, Companies and Websites. Users and companies have websites, and thus each user record has a foreign key into the Websites table. Also, each company record has a foreign key into the Websites table.

Now I want to include foreign keys in the Websites table back into their respective "parent" records. How do I do that? Should I have two foreign keys in each website record, with one of them always NULL? Or is there another way to go?

Answer 1

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

1. 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
)