1

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?

0

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.

  • 1
    Thanks, I analyzed your solution and it is what I wanted. – M_Armendariz Jun 14 '18 at 2:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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