多个但互相排斥的外键 - 是这样的方式吗？ [英] Multiple yet mutually exclusive foreign keys - is this the way to go?

问题描述

我有三张表：用户，公司和网站。
用户和公司都有网站，因此每个用户记录在网站表中都有一个外键。此外，每个公司的记录都有一个外键进入网站表。

现在我想将外键包含在网站表中，回到各自的父记录中。我怎么做？每个网站记录中是否应该有两个外键，其中一个总是空的？或者还有另外一种方法吗？

解决方案

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

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

第三个关系意味着存在一个用户或公司实体，其 PRIMARY KEY 应存储在某个地方。

要存储它，您需要创建一个表，它将存储网站所有者 PRIMARY KEY $ c>实体。此表还可以存储用户和网站的常用属性。

由于它是一对一关系，因此网站属性也可以存储在此表中。 / p>

用户和公司不共享的属性应存储在单独的表中。

强制正确的关系，您需要使用所有者类型网站复合的 PRIMARY KEY c $ c>作为其一部分，并使用 CHECK 约束强制在子表中的正确类型：

  CREATE TABLE website_owner（
 type INT NOT NULL，
 id INT NOT NULL，
 website_attributes，
 common_attributes，
 CHECK （类型IN（1，2）） -  1为用户，2为公司
 PRIMARY KEY（类型，id）
）
 
 CREATE TABLE用户（
键入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）参考website_owner 
）
  

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?

解决方案

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
)

这篇关于多个但互相排斥的外键 - 是这样的方式吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！