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.
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:
To get all symptoms of a combination of
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
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'