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

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.

要强制建立正确的关系,您需要将 websitePRIMARY KEYowner 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
)

相关文章