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
Project belong to
Company and may concern any number of company's
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?)