Medical database design and queries optimization I have created a MySQL database which will be used in a medical app, but I am pretty sure the design and the used queries can be improved, since I am new in database design. My biggest doubt is the s_apply column in combination table. The user should choose 1 main symptom (s_id). Main symptoms may have 0, 1 or 2 symptoms that apply (s_apply). I know it is bad practice to store the full names in s_apply, but I don't know how to improve this. I really need a way to store the symptoms that apply (s_apply) more efficient. 

The d_weight in disease indicates if a disease in that context is either common (d_weight &lt;= 5) or less common (d_weight > 5). 

Combination table has 2 foreign keys: s_id and d_id.

To get all symptoms of a combination of age, gender and bodypart, I use the following query. There should be a better way to do this:

SELECT DISTINCT symptom.s_id, s_name, s_common FROM symptom, combination WHERE combination.age = '$age' AND combination.gender = '$gender' AND combination.bp_id = '$bodypart' AND combination.s_id = symptom.s_id 


To get all diseases of a combination of age, gender, bodypart, s_id and symptoms that apply (s_apply), I use the following query:

SELECT DISTINCT disease.d_id, d_name, d_weight FROM disease, combination, symptom WHERE combination.age = '$age' AND combination.gender = '$gender' AND combination.bp_id = '$bodypart' AND combination.s_id = symptom.s_id AND combination.s_id = '$s_id'


Symptom table



Disease table



Combination table
The Symptom and Disease tables are well-conceived.
Their purpose is clear, and they are about specific thing each.

The Combination table on the other hand stinks.
Combination of what?
It seems your goal with this table is to represent:


One record per case, maybe?
Age, gender, body part, weight, disease, main symptom of the case
Additional symptoms


Instead of putting the addition symptoms as a concatenated list of values,
it would be better to create another table with:


foreign key on case id, and symptom id
unique constraint on case id + symptom id


Also,
the prefixing of column names with s_ for symptom and d_ for disease is not great. It would be better to drop these prefixes.
You can use table aliases s and d instead, for example:

SELECT DISTINCT s.id, s.name, s.common FROM symptom s, combination c 
WHERE c.age = '$age' AND c.gender = '$gender' AND c.bp_id = '$bodypart' AND c.s_id = s.id