In the er diagram below (not mine),
A Class can have many instructors via a standard junction table. Is there a way of designing the Instructors/Classes tables using constraints to ensure that a Class must have at least one Instructor? I.e. 1 or many instead of 0 or many? So given Classes.class_id=1, there must be an Instructors_Classes(class_id=1, instructor_id=?) Or is this something that you'd just use triggers to enforce? As a secondary, can anyone suggest a good way of identifying one, and only one Instructor as the primary instructor for a class? Currently I've added a field Instructors_Classes.IsPrimary (number) and applied a unique constraint on (class_id, IsPrimary) and a validation constraint of IsPrimary=Null OR 1, so only 1 instructor can have the value 1 per Class. Then when I want to get a list of all Classes, I can just join on Instructors_Classes.class_id WHERE IsPrimary=1 so that I get a single value for the Instructor of that class. This just feels a bit wrong but I'm not a db person so I don't know if there's a more conventional way of doing it. I thought of adding a Classes.instructor_id field but couldn't work out how to enforce the link between it and the Instructors_Classes table.

A Class can have many instructors via a standard junction table. Is there a way of designing the Instructors/Classes tables using constraints to ensure that a Class must have at least one Instructor? I.e. 1 or many instead of 0 or many? So given Classes.class_id=1, there must be an Instructors_Classes(class_id=1, instructor_id=?) Or is this something that you'd just use triggers to enforce? As a secondary, can anyone suggest a good way of identifying one, and only one Instructor as the primary instructor for a class? Currently I've added a field Instructors_Classes.IsPrimary (number) and applied a unique constraint on (class_id, IsPrimary) and a validation constraint of IsPrimary=Null OR 1, so only 1 instructor can have the value 1 per Class. Then when I want to get a list of all Classes, I can just join on Instructors_Classes.class_id WHERE IsPrimary=1 so that I get a single value for the Instructor of that class. This just feels a bit wrong but I'm not a db person so I don't know if there's a more conventional way of doing it. I thought of adding a Classes.instructor_id field but couldn't work out how to enforce the link between it and the Instructors_Classes table.
Asked by Octothalamus
(13 rep)
Jun 20, 2016, 08:56 PM
Last activity: Jun 21, 2016, 12:14 PM
Last activity: Jun 21, 2016, 12:14 PM