I am designing a database for a kindergarten and I got stuck trying to design a relationship between three entities.

In this school parents can authorize certain people to pick up their kids when they can't do so. So there should be three entities: child, parent and AP (authorized person). A child should have at least one parent, two at maximum. A parent should have at least one child, but they can have many. So I designed this as a many to many relationship.

The parent can authorize zero or many people to pick up their child(ren), and the same person can be authorized by many parents to do so. Hence a many to many relationship exists in my design between PA and Parent.

What is a design for a table to keep track of who has picked up a child? The table should be related with the child table and save the date, but a parent or a AP can pick up the child, so how is that relationship modeled? I have never seen this design pattern before, but if there were a way to design inheritance in SQL this should be easy; an AP is just a specialized form of parent, so both entities can be treated as the same when picking up the child from school.

Maybe there should be two tables, one to model when a parent picks up a child and one where the AP picks up a child. Maybe an AP should be related directly to the child.

CodePudding user response：

Get rid of the AP entity and rename Parent with Adult which will contain parents authorized persons. Your design could look like this:

child (1, n) (0, n) adult

[adult] parent (0, n) (1, n) [adult] authorized person

A child has at least 1 parent. An adult can have 0 child (authorized person) or many (parent). A parent (adult entity) can authorized 0 or more AP and an AP (adult entity too) is authorized by at least 1 parent.

Then for pickups you only need relationships with the adult entity and the child entity.
Note that this design will allow a parent to be the AP for other children.