0

Assume I use MySQL, and there are two tables, students and parents_guardians table. Table students is used to store students' data. The parents_guardians table is used to store students' parents and students' guardians.

So either a student can have a father and a mother as his parents, or a student can have an uncle and an aunt as his guardians. To do that, I add one more table as pivot table, which is students_parents_guardians table.

This pivot table contains 3 columns:

  1. students_id
  2. parents_id
  3. guardians_id

The question is: Is this good design? And how can I do that in Laravel?

So far, I'm unable to do that. What I can do is create 5 table:

  1. students table to store student's data
  2. parents table to store parents' data
  3. students_parents as pivot table to perform relationship between students and parents.
  4. guardians table to store guardian's data
  5. students_guardians as pivot table to perform relationship bwtween students and guardians.

I believe this is a bad idea, and I should normalize all of it.

Improve this question
9
  • 1
    Hi, and welcome to the forum! A linking table is what you are looking for! But why have separate parent and guardian tables? A parent is automatically a guardian. Have a guardian table with an is_parent Boolean field! 5 tables -> 3!
    – Vérace
    Dec 25, 2020 at 23:50
  • Both guardians and parents I think it should be on the same table. But in this case I should separate it in different tables because I got some difficult in perform a relationship if we put them in the same table. Do you have a solution to do this?
    – Sa'ad Abdurrazzaq
    Dec 26, 2020 at 3:55
  • 1
    Not much more complexity (WHERE is_parent = [TRUE | FALSE])! Plus, it will greatly simplify queries for both, and, as has been pointed out, the distinction between parent and guardian for a school is probably minimal!
    – Vérace
    Dec 26, 2020 at 6:33
  • I am using Laravel to implement it, it's such a bit difiicult to explain here, because it will take many lines of code. Could you show me the more detail clue and tutorial?
    – Sa'ad Abdurrazzaq
    Dec 26, 2020 at 6:46
  • 1
    I've never used Laravel (or PHP for that matter) so I can't help out with any tutorials - you'd be better placed to know a decent one than I would! AFAICS, it's pretty straightforward - just create a guardian table with an is_parent BOOLEAN field Very important - what version of MySQL are you using?
    – Vérace
    Dec 26, 2020 at 7:41

1 Answer 1

Reset to default
2

Your design with 5 tables is more normalized than your initial design, and makes more sense.

When you say "pivot table" it sounds like you mean a linking table. Since the relationship between students and parents (and also between students and guardians) is a many-to-many relationship being that a student can have up to two parents, and a parent can have multiple children (students) then this is the proper way to relate the two tables in a normalized form, and good design.

The only thing I think you could do is consolidate the parents and guardians into one Table (assuming they have basically the same attributes) and have a type field to determine if it's a parent or guardian. If there's only two types, you can use a boolean datatype field called IsParent, or with more types you can create an enum table called RelationshipTypes (or something similar) and store the ID in your consolidated Table, for normalization.

The IsParent field would be best to put on the linking table, that way a Guardian can be a parent to one Student but only a Guardian to another Student (who isn't related by blood) in cases of like adoption, etc.

Here's a sqlfiddle to illustrate the above, with some tweaks.

Improve this answer
12
  • I would avoid the ENUM type if possible!
    – Vérace
    Dec 25, 2020 at 16:15
  • @Vérace Sorry I wasn't implying the ENUM datatype (I thought that was explicit enough by saying the ID would be stored in the Parent/Guardian table, and the value in another table). Rather I meant a table with a fixed number of rows to represent an "enum". Just updated my answer to clarify that, thanks for pointing that out.
    – J.D.
    Dec 25, 2020 at 16:35
  • 2
    Definitely this. And absolutely consolidate the parent and guardian table. In a system you track the data that you care about. The biological distinction between a parent and a guardian might be of interest in some systems, but in a school system there is absolutely nothing important about the distinction between a parent and a guardian. They are legally the same as far as the school is concerned and will have all of the same attributes and relationships, so there's no good reason at all for keeping these two types of data in different tables.
    – Joel Brown
    Dec 25, 2020 at 17:43
  • Reorganizing tables for subtyping is not normalization.
    – philipxy
    Dec 25, 2020 at 22:57
  • @Philipxy That's potentially debatable, but the part that my answer states is normalization is using a RelationshipTypes table to refactor out the value and only reference the type by ID
    – J.D.
    Dec 26, 2020 at 1:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.