2

I am designing an app where I want to search for diseases based on symptoms. I am trying to design the database using MySql, but as a beginner I run into some trouble with the design philosophy.

Basically a disease is going to have multiple symptoms, so on the disease table I wanted to have these columns:

disease_id, disease_name, disease_description, **disease_symptoms**

The thing is, one disease is not going to have only a single symptom, and putting multiple values in a single cell goes against DB design philosophy from what I found out online.

So the best solution I found so far was to make a table with multiple columns, 1 for disease id and then like 10 columns called: symptom_0, symptom_1, symptom_2, ect. So when I do a SQL query it can return all the diseases that have a specific symptom.

I was just wondering if there is a more efficient way to do this.

Yannis P.
  • 811
  • 1
  • 11
  • 32

3 Answers3

9

If you have M-to-N relation between diseases and symptoms (or any other entities, actually), you need 3 tables: first for diseases, like (disease_id, ...), second for symptoms (symptom_id, ...) and the third one, linking table, (disease_id, symptom_id).

Then, you executing SQL like: SELECT * FROM diseases WHERE disease_id IN (SELECT disease_id FROM diseases_vs_symptoms WHERE symptom_id = MY_SYMPTOM);

UPDATE I've just googled a bit and found this promisingly looking SO question, which you may find useful: Relational Database Design Patterns?

Community
  • 1
  • 1
Victor Sorokin
  • 11,878
  • 2
  • 35
  • 51
1

I don't think it's a good idea to create multiple columns, since there are a lot of possible symptoms. The way i'd do it: Create table symptoms with id and name Create table disease_symptoms with disease_id and symptom_id

Now, in table disease_symptoms you've linked diseases and symptoms, and can easily find which diseases have some symptoms and the other way around.

Darvex
  • 3,624
  • 2
  • 23
  • 39
1

I would create two more tables. One just for symptoms with two fields: symptom_id and symptom_name. Second, connecting your diseases with symptoms, two fields: disease_id and symptom_id. Second table will have as many entries as there is symptoms for that disease.

sskoko
  • 819
  • 6
  • 18