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.
Post History
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...
#2: Post edited
by
toraritte
·
2023-10-10T22:38:36Z (7 months ago)
- Found the example below in a book teaching logical database design, but I'm stuck on how one would implement this in PostreSQL:
- [![enter image description here][1]][1]
- The diagram reflects the following business rule:
- > An instructor must teach one class, but no more than eight classes.
This what I infer from the diagram:- + There is a 1:N (one-to-many) relationship between the `INSTRUCTORS` and `INSTRUCTOR CLASSES` tables.
- + Every record (row) in the `INSTRUCTORS` table has to have at least one corresponding record (row) in the `INSTRUCTOR CLASSES` table - and vice versa.
- + `(R)` represents the `RESTRICT` deletion rule. It appears on both sides of the relationship, so in order to delete a record (row) from the `INSTRUCTORS` table there shouldn't be any record (row) in `INSTRUCTOR CLASSES` table references it - and vice versa.
- The last item is what makes my head scratch:
- 1. 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...)
- 2. If it is possible to implement this, then how would one delete a record (row) from the `INSTRUCTORS` table? (Or the last assignment for an instructor in the `INSTRUCTOR CLASSES` table.) Via transactions?
- 3. 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.
- [1]: https://i.stack.imgur.com/C0Ft7.png
- Found the example below in a book teaching logical database design, but I'm stuck on how one would implement this in PostreSQL:
- [![enter image description here][1]][1]
- 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 `INSTRUCTORS` and `INSTRUCTOR CLASSES` tables.
- + Every record (row) in the `INSTRUCTORS` table has to have at least one corresponding record (row) in the `INSTRUCTOR CLASSES` table - and vice versa.
- + `(R)` represents the `RESTRICT` deletion rule. It appears on both sides of the relationship, so in order to delete a record (row) from the `INSTRUCTORS` table there shouldn't be any record (row) in `INSTRUCTOR CLASSES` table references it - and vice versa.
- The last item is what makes my head scratch:
- 1. 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...)
- 2. If it is possible to implement this, then how would one delete a record (row) from the `INSTRUCTORS` table? (Or the last assignment for an instructor in the `INSTRUCTOR CLASSES` table.) Via transactions?
- 3. 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.
- [1]: https://i.stack.imgur.com/C0Ft7.png
#1: Initial revision
by
toraritte
·
2023-10-10T18:01:08Z (7 months ago)
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: [![enter image description here][1]][1] The diagram reflects the following business rule: > An instructor must teach one class, but no more than eight classes. This what I infer from the diagram: + There is a 1:N (one-to-many) relationship between the `INSTRUCTORS` and `INSTRUCTOR CLASSES` tables. + Every record (row) in the `INSTRUCTORS` table has to have at least one corresponding record (row) in the `INSTRUCTOR CLASSES` table - and vice versa. + `(R)` represents the `RESTRICT` deletion rule. It appears on both sides of the relationship, so in order to delete a record (row) from the `INSTRUCTORS` table there shouldn't be any record (row) in `INSTRUCTOR CLASSES` table references it - and vice versa. The last item is what makes my head scratch: 1. 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...) 2. If it is possible to implement this, then how would one delete a record (row) from the `INSTRUCTORS` table? (Or the last assignment for an instructor in the `INSTRUCTOR CLASSES` table.) Via transactions? 3. 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. [1]: https://i.stack.imgur.com/C0Ft7.png