Let me first clarify what “mandatory” means. I mentioned already in Entity Relationship Diagram (ERD) Part 5 that: If an employee (considering the example given) must work on a project (meaning must work for at least one or more project) this sketched by double lines, and called “total or mandatory participation”.
What I understood from you is, every employee must work on all the projects (considering the same example). If so, then use triggers.
Whenever you insert a new employee, cascade down and insert all the project primary keys + the new employee primary key in the linking (or referencing table). That’s what the trigger supposed to do.
If you just need to make sure that every employee must work on at least one or more project, then whenever you insert a new employee, assign a project, and insert the new employee primary key + assigned project primary key in the linking (or referencing table).
— — —
The above formula seems to work only if the relationship is optional on the many side.
The above formula is for structuring and the rules that should be enforced to keep your database tables consistent (i.e. You can’t have two rows with the same employee, and the same project). It doesn’t care about if Adam works on Project A or not.
Hope It helps!
