0

I recently made a simple database for a family member. The database stores individuals and their close family members these include siblings. The issue is, unlike parents, siblings are variable and an individual could have 1 or 12.

A simplified version of my SQL table looks like this:

| name | age | parents | siblings |
|      |     |         |          |
|      |     |         |          |

Currently siblings are condensed into a single string and stored into the siblings column with separators.

Example: "john%doe%15 & liam%doe%17 & james%doe%23"

The actual string has 20 odd fields which makes packing and unpacking the string messy. It also makes adding new fields hard as changing the order the data is stored will cause it to load incorrectly. I've looked around and noticed many databases must face the similar issues, is there some kind of industry standard or better method?

Cheers

Mahaki
  • 3
  • 1
  • 2
    You want to use a separate table for siblings, with a foreign key that points back to the parents. This is basic RDBMS usage. See https://stackoverflow.com/questions/757181/basics-of-foreign-keys-in-mysql – kmoser Jan 03 '22 at 06:11

1 Answers1

0

This is a clear example of N:M (cardinality). What you have to create is an "intersecting table" which connects IDs of two separate tables. Since you can have multiple data on both ends.

In this case you will create a separate table for parents and siblings as well asperson_parents(id_person, id_parent) and person_siblings(id_person, id_sibling) tables. Keep in mind you will need to create the parents and siblings as a person as well with this method or you can choose to write the name and surname instead.

Tilen
  • 484
  • 1
  • 15