0

I have a practice problem on Database implementation and am confused. For this database, I need to store enrollment information on students. Information such as which units a student has enrolled into. The relationship between Units and Students is such that a student can enroll in up to 4 units per semester but a unit can be enrolled into by many students. My problem is, how can I implement this limitation of a student enrolling in up to only 4 units per semester in mysql using either CHECKs or triggers?

If it was one to many relationship between Student and Units that would have been straight forward but here we have a one to four(?) relationship?

Improve this question
4
  • 1. business rules should be enforced via logic, not your database table design 2. you can't enforce CHECKs across rows (except exclusion constraints, sorta) 3. therefore you must use a trigger, or application logic
    – Neil McGuigan
    Commented Mar 2, 2021 at 7:11
  • @NeilMcGuigan how would such a trigger be implemented?
    – chimmy102
    Commented Mar 2, 2021 at 7:19
  • use a before insert trigger, and count the right rows
    – Neil McGuigan
    Commented Mar 2, 2021 at 7:21
  • CHECK cannot help - it checks the values in one current row only.
    – Akina
    Commented Mar 2, 2021 at 8:04

1 Answer 1

Reset to default
1

I see at least 2 possibilities.

First - BEFORE INSERT / UPDATE triggers which checks the amount of enrolls per student and forbids insertion / updation if the restriction fails. UPDATE trigger needed for to restrict errorneous data while updating with student ID change.

Second - add enroll_number column which is NOT NULL, ENUM('1','2','3','4') and UNIQUE in the combination with student ID.

I am not considering the option where the table contains 4 fields for 4 separate enrolls - this is denormalized structure and bad practice. Formally the same for a variant when all enrolls are stored in one column of some serialized datatype (JSON, XML).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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