I have following database design (by =>
foreign key constraints are depicted):
Company[id] CompanyRealm[id, company_id=>Company.id]many2many Project[id, company_id=>Company.id] ProjectRealm[id, project_id=>Project.id, company_realm_id=>CompanyRealm.id]many2many
Problem is that provided database design allows inconsistent data. For example:
Company1(id=1) Company2(id=2) CompanyRealm(id=11, company_id=1) Project(id=33, company_id=2) ProjectRealm(id=44, project_id=33, company_realm_id=11)
(Company
can have many Realms
, Project
belong to Company
and may concern any number of company's Realms
)
ProjectRealm refers to two different companies:
- Company1 (via CompanyRealm); and
- Company2 (via Project).
Is something wrong with my DB design?
If yes - which prescriptions are violated?
If no - how to prevent incorrect data insertion (via constraint? trigger with exception?)