Database Design – How to Store Array Values

database-design

Maybe this question has been asked already, but I don't know if my approach is correct.

Once I was modelling a database for my university. It was meant to store information about the health of the students, and then analyze it to give the student a feedback.

For example: in one question it was asked who have or had diabetes, with multiple options as an answer, for example:

  • Mom
  • Dad
  • Student
  • Brothers

And the student could select one or many options. I needed to store that in the database.

First the values in the DB where stored in a string where values were separated by a comma. (I know, this isn't the best way).

So I thought about doing the next things:

[Table[1]

I thought about creating a table for each field where the value was stored like a comma separated array.

Table2

Having the same fields, but just to identify which relative has/had the disease.

What I did was correct?

Best Answer

Student table:

id | name

In this table you will have the students.

Sickness table:

id | name

In this table will have the sicknesses. It will have a row for all the sicknesses (cancer, diabetes, and so on).

Relative table:

id | name

This table will have the relations: "mom", "dad", "brother" and so on.

The trick what you need is a bridge table, or associative entity.

These are used to map many-to-many relations. And.. much more.

Because in this case, you need more than a single many-to-many relation. In fact, you need a "many-to-many-to-many relation". Essentially, it is an associative entity between 3 entities.

It is uncommon, but possible and can be handled pretty well in the DB design.

So, you need to have also a fourth table, with the following columns:

student_id | relative_id | sickness_id

If the table has a row, for example, (3, 4, 5), it would mean that the relative with id 4 of student with id 3, had the sickness with id 5.

On this way, you don't need multiple tables for the different sicknesses, it would be a very bad design.

(student_id, relative_id, sickness_id) should be a primary key of this bridge table. The induvidual columns will be also foreign keys to the corresponding student, relative or sickness tables.

Related Question