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

  • A group can have many students and a student can belong to many groups? Jul 4, 2013 at 19:22
  • Yes, a group can have many students and a student can belong to many groups
    – porton
    Jul 4, 2013 at 20:04

1 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.)

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


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

      Group  PK 
      Topic  PK     FK -> Topics  

      Pupil  PK

      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:

      Group   PK 
      Topic      UQ1 FK -> Topics
      OrderNo    UQ1

      Pupil   PK

      Pupil   PK     FK1 -> Pupils
      Topic   PK     FK2 -> Groups
      OrderNo        FK2 
  • 1. I don't understand your notation. 2. Topic in PupilParticipatesInGroup seems superfluous because the topic is unambiguously determined by a group
    – porton
    Jul 4, 2013 at 19:39
  • Your solution requires me to add redundant Topic field to the PupilParticipatesInGroup table. For having no superfluous fields, isn't it better to use transactions instead of UNIQUE indexes and FKeys?
    – porton
    Jul 4, 2013 at 20:03
  • Using transactions is good yes. But that doesn't mean you can remove Primary, Unique and Foreign key constraints. Who is going to take care of the integrity then? The transactions? How? Jul 4, 2013 at 20:10
  • Can your solution be modified not to add superfluous PupilParticipatesInGroup.Topic field?
    – porton
    Jul 4, 2013 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.