-2

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.

ERD

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Jacob Morales Gonzalez
  • 103
  • 3
  • Thanks @SadlyFullStack, but each parent can authorize between 0-5 APs, this is how is done today, but if the system goes to production there should be "no limit" (I should have stated that) – Jacob Morales Gonzalez Sep 01 '22 at 14:22
  • do you mean that in such table the difference between AP and Parent is the relatinship_type with the child? @SadlyFullStack – Jacob Morales Gonzalez Sep 01 '22 at 14:32

1 Answers1

2

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.

AymDev
  • 6,626
  • 4
  • 29
  • 52
  • Another piece of context @AymDev an AP just have a subset of attributes that a Parent have, could the extra space allocated for AP be a problem? – Jacob Morales Gonzalez Sep 01 '22 at 14:42
  • 1
    @JacobMoralesGonzalez Unless you have a very high number of children/adults I don't think so. But you might want to optimize your design anyway (for performance on read or write, or for storage space). I have no idea about the parent attributes an AP would not have but you may put those attributes in the join table between child and adult. – AymDev Sep 01 '22 at 14:58