How to represent it with UNIQUE indexes and foreign keys?

Question

"Group" is a group of pupils. "Topic" is a topic of lessons.

To every group corresponds exactly one topic. (So there may be multiple groups per topic.)

To a pupil corresponds multiple groups, but maximum one group per topic.

How to implement this in terms of UNIQUE indexes and foreign keys in MySQL?

Answer 1

This is a common problem. The "trick" is to:

• add in Groups - besides the Primary Key - a redundant Unique constraint on (Topic, Group).
• this allows you to add the Topic attribute in the Participates relation and
• forces you to modify the Foreign Key constraint from Participates to Groups so it includes Topic, not only Group.

• then you can add a Unique constraint on Participates (Pupil, Topic) (meaning a pupil can participate only once in a topic, not twice or more, which is the wanted result.)

  Topics
    Topic  PK
  
  Lessons
    Lesson PK 
    Topic         FK -> Topics
  
  Groups
    Group  PK UQ1
    Topic     UQ1 FK -> Topics  
  
  Pupils
    Pupil  PK
  
  PupilParticipatesInGroup
    Pupil  PK UQ1 FK1 -> Pupils
    Group  PK     FK2 -> Groups
    Topic     UQ1 FK2 
  

Appendix

 Abbreviation   Constraint
 ------------   -----------
   PK           Primary Key
   UQ           Unique 
   FK           Foreign Key

Note that the design is in 3NF and that the redundant Unique constraint on Topics is only needed for the implementation in most DBMS (so the Foreign Key is enforced.)

The only other issue is the Group -> Topic dependency in the Participates relation which makes the schema to violate BCNF.

In the linked page, in paragraph Achievability of BCNF, you'll see that it has been proven that not all tables can be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. The provided example is striking similar to the Participates table here:

In some cases, a non-BCNF table cannot be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. Beeri and Bernstein showed in 1979 that, for example, a set of functional dependencies {AB → C, C → B} cannot be represented by a BCNF schema.[6] Thus, unlike the first three normal forms, BCNF is not always achievable.

Lets make one more change nonetheless, in order to remedy that (if we can!):

---

Lets examine what happens if we remove (as if it never existed) the Primary Key (Group) constraint from Groups (and put the Unique in its place.) Lets also change the Primary key in Participates - notice - to be exactly the Unique constraint that is our target from the beginning. The design becomes (first two tables omitted as they remain intact):

    Groups
      Group  PK 
      Topic  PK     FK -> Topics  

    Pupils
      Pupil  PK

    PupilParticipatesInGroup
      Pupil  PK     FK1 -> Pupils
      Group         FK2 -> Groups
      Topic  PK     FK2 

No redundant columns at all. Magic! The Participates is a common many-to-many relation between Groups and Pupils (as our model wants) and the two Foreign Keys are referencing the Primary Keys of the corresponding tables.

So, what happened? Magic? (not really, no) And isn't the Group still a candidate key in Groups? (removing it was only a thought experiment anyway.) Don't we lose something by omitting this constraint from the actual tables?

---

The issue - and why the above is solving it - is that the Unique constraint you are trying to model needs the Topic attribute in the Participates relation. So, you have to use a candidate key in Groups which is compound and includes Topic. It doesn't necessarily have to be (Topic, Group). It could be some other combination. What other attributes does Groups have?

If, for example, there is an attribute OrderNo in Groups and (Topic, OrderNo) is unique, then we could have this design (at last!) where there is no redundancy, no superfluous attributes, all constraints are there and the schema is in BCNF:

    Groups
      Group   PK 
      Topic      UQ1 FK -> Topics
      OrderNo    UQ1

    Pupils
      Pupil   PK

    PupilParticipatesInGroup
      Pupil   PK     FK1 -> Pupils
      Topic   PK     FK2 -> Groups
      OrderNo        FK2