如何正确设计数据库的这一部分（循环引用？） [英] How to properly design this part of a database (circular reference?)

问题描述

情况：

一家公司有很多项目

一个项目有很多标签


一个项目只能属于1个公司

标签可以属于多个项目


一个公司必须拥有自己的访问权限标签

A company has many projects
A project has many tags

A project belongs to only 1 company
A tag can belong to multiple projects

A company must have access to its own tags

示例1：

在第一张图片中，可以通过projects / project_tag获得公司的所有标签。但是，如果所有项目都被删除，则该公司的标签将不再可用，因为project_tag和项目之间的链接已消失。
即使没有项目，标签也应始终以某种方式链接到公司。

In the first image, all tags for the company are available through the projects/project_tag. But if all projects are removed, then the tags for the company will not be accessible anymore, because the link between project_tag and projects is gone. The tags should be somehow always be linked to the company, even if there are no projects.

示例2（标签也链接到公司） ：

Example 2 (where tags are also linked to the company):

在第二张图片中，工作，但这现在是循环参考吗？？？
对于这样的问题，最好的解决方案是什么？
那么外键呢？

In the second image, it should work, but is this now a 'circular reference' ??? What should be the best solution for a problem like this? And what about foreign keys?

问题最后是：
如何正确设置

在第二个示例中可能出错的示例：

Example when things could go wrong in the second example:

companies:
id=1, name=MyCompany
id=2, name=OtherCompany

tags:
id=1, company_id=1, name=MyTag
id=2, company_id=2, name=OtherTag

projects:
id=1, company_id=1, name=MyProject

project_tag:
project_id=1, tag_id=1
project_id=1, tag_id=2 --> THIS ROW IS NOT VALID!

最后一个project_tag行无效，原因是：

项目1 链接到 company_id 1

tag_id 2 链接到 company_id 2

---

已更新： 感谢所有人提供的信息！

---

UPDATED: Thanks all for the information!

基于已接受的答案，对PostgreSQL的CREATE查询将变为：

Based on the accepted answer, the CREATE queries for PostgreSQL would become:

CREATE TABLE companies (
   id SERIAL PRIMARY KEY NOT NULL,
   name TEXT NOT NULL
);
CREATE TABLE projects (
   id SERIAL PRIMARY KEY NOT NULL,
   company_id INT NOT NULL,
   name TEXT NOT NULL,
   UNIQUE (id, company_id),
   FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE tags (
   id SERIAL PRIMARY KEY NOT NULL,
   company_id INT NOT NULL,
   name TEXT NOT NULL,
   UNIQUE (id, company_id),
   FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE project_tag (
   id SERIAL PRIMARY KEY NOT NULL,
   company_id INT NOT NULL,
   project_id INT NOT NULL,
   tag_id INT NOT NULL,
   UNIQUE (company_id, project_id, tag_id),
   FOREIGN KEY (company_id, project_id) REFERENCES projects (company_id, id) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY (company_id, tag_id) REFERENCES tags (company_id, id) ON DELETE CASCADE ON UPDATE CASCADE
);

经过测试：

-在project_tag中插入的行在相同的company_id 上进行检查（否则：
被拒绝）

-无法插入重复行进入project_tag

-如果删除了项目，则链接的 project_tag行也被删除了

-如果删除了标签，链接的 project_tag行也会被删除

-如果公司在进行项目开发时被删除，则删除被拒绝（请参见项目表：ON DELETE RESTRICT）

-如果公司（没有项目）被删除，所有链接的标记也被删除

Tested:
- Rows inserted in project_tag are checked on the same company_id (else: denied)
- Not possible to insert duplicate rows into project_tag
- If a project is removed, the linked project_tag rows are also removed
- If a tag is removed, the linked project_tag rows are also removed
- If a company is being removed while still having projects, the removal is rejected (see projects table: ON DELETE RESTRICT)
- If a company (without projects) is removed, all linked tags are removed also

推荐答案

首先，您的第二个模型是绝对正确的，并且其中没有任何循环引用。

First of all, your second model is absolutely correct and there is not any circular reference in it.

您应传输 Company 中的 Company_ID 作为 FK 到标记和 Project ，并将其设置为 Not Null 。

You should transmit Company_ID of Company as F.K to Tags and Project and make it Not Null.

然后，您应按以下方式传输 TAG_ID 和 Project_ID FK 放入 Project_Tag 并使其唯一。并且无需传输项目和标签 Company_ID c>（我们在上一段中传输了）到 Project_Tag 。

Then, you should transmit TAG_ID and Project_ID as F.Ks into Project_Tag and make the unique together. And there is no need to transmit the Company_ID of Project and Tag (that we transmitted in previous paragraph) into Project_Tag.

现在，最后一个问题怎么样，您的最后一个请求：

Now, how about final question, Your final request:

此行无效！

THIS ROW IS NOT VALID!

您无法通过ER捕获它。您应该编写一些函数，触发器或存储过程来捕获和控制它。

You can not capture it by ER. You should write some functions, triggers or stored procedures to capture and control it.

编辑：

基于@reaanb的评论和他的出色回答此处：您可以通过这种方式来控制此约束，但需要一点冗余：

Edit:
Based on @reaanb's comments and his great answer here: You can control this constraint by this way with a little redundancy:

CREATE TABLE Project(
    project_id INT NOT NULL,
    company_id INT NOT NULL,
    PRIMARY KEY (project_id),
    FOREIGN KEY (company_id) REFERENCES Company (id),
    UNIQUE KEY (project_id, company_id)
);

CREATE TABLE Tag(
    tag_id INT NOT NULL,
    company_id INT NOT NULL,
    PRIMARY KEY (tag_id),
    FOREIGN KEY (company_id) REFERENCES Company (id),
    UNIQUE KEY (tag_id, company_id)
);

CREATE TABLE Project_Tags(
    id INT NOT NULL,
    company_id INT NOT NULL,
    project_id INT NOT NULL,
    tag_id INT NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY (tag_id, project_id)

    FOREIGN KEY (project_id, company_id) REFERENCES Project (project_id, company_id),
    FOREIGN KEY (tag_id, company_id) REFERENCES Tag (tag_id, company_id),
);

这篇关于如何正确设计数据库的这一部分（循环引用？）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！