情况:
一个公司有很多项目
一个项目有很多标签
一个项目只属于1家公司
一个标签可以属于多个项目
公司必须有权访问自己的标签
示例1:
在第一张图片中,公司的所有标签都可以通过projects/project_tag 获得。但如果所有项目都被删除,那么该公司的标签将无法再访问,因为project_tag和项目之间的链接消失了。
即使没有项目,标签也应该以某种方式始终链接到公司。
示例 2(其中标签也链接到公司):
在第二张图片中,它应该可以工作,但这现在是“循环引用”吗???
对于这样的问题,最好的解决方案应该是什么?
那么外键呢?
The question最后是:如何针对这种情况正确设置数据库/数据模型?
第二个示例中可能出现问题的示例:
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链接到公司 ID 1
tag_id 2链接到公司 ID 2
UPDATED:感谢大家提供的信息!
根据已接受的答案,PostgreSQL 的 CREATE 查询将变为:
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
);
Tested:
- 在project_tag中插入的行在相同的公司 ID(别的:
被拒绝)
- 无法插入复制行放入project_tag
- 如果项目被删除,链接的项目标签行也被删除
- 如果删除标签,则链接的项目标签行也被删除
- 如果公司在仍有项目的情况下被删除,则删除将被拒绝(参见项目表:删除限制)
- 如果删除公司(没有项目),所有链接的标签也会被删除