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.

Answer 1

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?