Database Cell with multiple values?

Question

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.

Community · Accepted Answer · 2017-05-23 11:53:40Z

10

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?

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Sep 18, 2013 at 8:54

Victor Sorokin

12k2 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darvex · Accepted Answer · 2013-09-18 08:56:12Z

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.

answered Sep 18, 2013 at 8:56

Darvex

3,6542 gold badges26 silver badges39 bronze badges

Comments

sskoko · Accepted Answer · 2013-09-18 08:57:35Z

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.

answered Sep 18, 2013 at 8:57

sskoko

8397 silver badges18 bronze badges

Collectives™ on Stack Overflow

Database Cell with multiple values?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related