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).
combinationCombination 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'
symptomSymptom table
diseaseDisease table
combinationCombination table
