Grounds for having a one to one relationship between tables

Question

If I have two different types of user, Parent and Child. They have identical fields, however a Child has a one to many relationship with exams, a relationship that does not exist for Parents.

Would Parent and Child best be modelled as a single table, or combined?

What if I have two different types of user, Parent and Child. They are the same apart from a child belongs to a school (a school has many children)

again, Would Parent and Child best be modelled as a single table, or combined?

Answer 1

I would break children out into a separate table for the simple reason that a parent may have many children (if not now, maybe in the future).

More than the current need, it is also important to consider what may happen in the future even if it doesn't make sense now. You may have two parent users who want to administrate one child and that child's exams.

Consider the most possible future functional requirements and program with them in mind.

Answer 2

They have identical fields, however a Child has a one to many relationship with exams

Even when fields are the same, different constraints¹ means you are dealing with logically separate entities. Absent other factors, separate entities should be put into separate physical tables.

There may, however, be reasons to the contrary. For example, if there is a key that needs to be unique across parents and children combined, or there is another table that needs to reference all of them etc...

If that's the case, then logically both "parent" and "child" are inheriting from the "person", containing the common constraints (and fields). Such "inheritance" can be represented by either storing the whole hierarchy into a single table (and setting unused "portion" to NULL), or by separating all three "classes" into their own tables, and referencing the "base class" from "inherited classes", for example²:

PERSON_ID is unique across all parents and children. In addition to that, OTHER_TABLE can reference it directly, instead of having to separately reference PARENT_ID or CHILD_ID.

---

¹ A foreign key in this case.

² A very simplified model that just illustrates the point above and does not try to model everything you mentioned in your question

Answer 3

Parent and Child both are Persons without a doubt. You should never put them in seperate tables.

Only time separates them : what if a Child becomes a parent?

A parent easily can have children for that you need a relationship table. Als a relationship table is the right way to model school membership.

so tables here :

person
is_child_of (many to many, join table) --> relations between persons can be is_parent_of

plain and simple

Remember : being a child is a relation from person to person. How would you model a grandchild if needed? Yet another table? And a great grandchild?

And supose you are fine with that and you make a lot of tables for a lot of "kind of" relationships, an all of a sudden you want you have to add a field (day of birth) or alter a field format : you have to do it in all your different tables.

Answer 4

If the items are modeled differently in your system, I would say that they should be different tables. Just because parents and children both have similar properties (names, ages, etc.), does not mean they will always have the same relationships with other entities in your database. You could have parent and child be in same table with column relating child to parent, but this just leads to awkward self-join queries when trying to represent this relationship. This in itself can become very odd if a child has two parents. So to me you would have a number of different tables:

parent
child
child_to_parent (many-to-many join table)
school
child_to_school (one school to many children)
classes
child_to_classes (many-to-many)
classes_exams (one-to-many table relating exams to classes)
child_to_classes_exams (many-to-many table relating children to exams for specific classes)
* and maybe things like the following
teacher
teacher_to_classes (many-to-many)

Now certainly in your class design (if you are using OOP), you could have child and parent extend from a common "person" class which would handle logic for setting common properties.

Answer 5

You have described three different entities in your question -- Parents, Child(ren), and Exams. You have shows how the three differ in the their relationships to each other.

From everything in your question, I would say that you have three entities and you should set them up as such in your database. That is, Parent and Child should have separate tables.