多个但互斥的外键 - 这是要走的路吗?

2021-12-26 00:00:00 sql mysql database-design data-modeling

我有三个表:用户、公司和网站.用户和公司都有网站，因此每个用户记录在网站表中都有一个外键.此外，每条公司记录都有一个指向网站表的外键.

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.

现在我想将网站表中的外键包含回它们各自的父"记录中.我怎么做?每个网站记录中是否应该有两个外键，其中一个始终为 NULL?或者还有别的方法吗?

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?

推荐答案

如果我们查看这里的模型，我们将看到以下内容:

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

  1. 一个用户只与一个网站相关
    • 一家公司只与一个网站相关
    • 一个网站只与一个用户或公司相关

第三个关系意味着存在一个用户或公司"实体，其PRIMARY KEY 应该存储在某处.

The third relation implies existence of a "user or company" entity whose PRIMARY KEY should be stored somewhere.

要存储它，您需要创建一个表来存储 website owner 实体的 PRIMARY KEY.该表还可以存储用户和网站共有的属性.

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.

要强制建立正确的关系，您需要将 website 的 PRIMARY KEY 与 owner type 复合作为其中的一部分，并使用 CHECK 约束强制子表中的正确类型:

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
)

相关文章