8

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?)

MDCCL
  • 8,520
  • 3
  • 30
  • 61
kakabomba
  • 183
  • 4

1 Answers1

8

You are right, the design allows inconsistencies, exactly what you notice. A ProjectRealm may be referring through Project to a company and through CompanyRealm to another company.

This is not uncommon, it appears when there is a triangular or a "diamond" shape in the relationships:

Realm          Company
   \           /     \
    \         /       \
     \       /         \
    CompanyRealm     Project
             \         /
              \       /  
               \     /    
             ProjectRealm

The common solution, if you want to enforce this via DRI (Declarative Referential Integrity), ie. through usual UNIQUE, FOREIGN KEY and CHECK constraints - and not though triggers - is to use composite keys for the ProjectRealm REFERENCES CompanyRealm and for the ProjectRealm REFERENCES Project foreign keys.

You will need to replace the company_realm_id with 2 columns (company_id, realm_id) and add a composite UNIQUE constraint on each of the two referenced tables (or modify their PRIMARY KEY):

Company [id]

Realm [id]

CompanyRealm [id, realm_id=>Realm.id, company_id=>Company.id,
              UNIQUE(company_id, realm_id)]

Project [id, company_id=>Company.id,
         UNIQUE(company_id, id)]

ProjectRealm [id, project_id, company_id, realm_id,
              (company_id, project_id) => Project (company_id, id),
              (company_id, realm_id) => CompanyRealm (company_id, realm_id)]

See also several other questions, where the same (or quite similar) problem appears. The diamond pattern is obvious in all:

Community
  • 1
ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
  • Thanks. In your solution tables looks like containing redundant data. In my opinion it is better than allowing inconsistent data. But in the name of art: are you solution violate some of NF? – kakabomba May 14 '16 at 16:26
  • 3
    No, if I am not wrong, they are 3NF, probably 5NF as well. It seems like the company_id is redundant in the ProjectRealm, true. In a way it is but since it's part of a UNIQUE key ((project_id, company_id, realm_id) is unique I assume) it doesn't violate any NF. I think I have discussed this is another question extensively, let me find the link. By the way, I would remove the CompanyRealm (id) and the ProjectRealm (id) if it was my design, unless they are there for specific, performance issues, with the DBMS you plan to implement this in. – ypercubeᵀᴹ May 14 '16 at 18:17