Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
How to implement a relationship between 2 tables where the type of participation is mandatory and RESTRICT rule for both?
Found the example below in a book teaching logical database design, but I'm stuck on how one would implement this in PostreSQL:
The diagram reflects the following business rule:
An instructor must teach one class, but no more than eight classes.
This is what I infer from the diagram:
There is a 1:N (one-to-many) relationship between the
Every record (row) in the
INSTRUCTORStable has to have at least one corresponding record (row) in the
INSTRUCTOR CLASSEStable - and vice versa.
RESTRICTdeletion rule. It appears on both sides of the relationship, so in order to delete a record (row) from the
INSTRUCTORStable there shouldn't be any record (row) in
INSTRUCTOR CLASSEStable references it - and vice versa.
The last item is what makes my head scratch:
As far as I know, deletion rules / constraints can only be specified on foreign key fields. So how would that work the other way around? Or this can only be implemented outside of PostgreSQL and in the application wrapper itself?
Given the business rule example, it makes sense: If one deletes all assignment for an instructor, then it will violate the rule. (Not saying that the rule is completely sensible though...)
If it is possible to implement this, then how would one delete a record (row) from the
INSTRUCTORStable? (Or the last assignment for an instructor in the
INSTRUCTOR CLASSEStable.) Via transactions?
Should I even bother worrying about this?
To me, it seems like a very contrived example: Yes, every instructor should teach at least one class, but if there is a new hire for example, they may not have an assigned class yet.