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 <= 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 tableDisease tableCombination table
Medical database design and queries optimization
sql;mysql
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 caseAdditional symptomsInstead 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 idunique constraint on case id + symptom idAlso,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 