Situation:
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
Example 1:
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.
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?
The question finally is: How to properly set up a database/datamodel for this situation?
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!
The last project_tag row is not valid because:
project 1 is linked to company_id 1
tag_id 2 is linked to company_id 2
UPDATED: Thanks all for the information!
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
);
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