Medical database design and queries optimization

Question

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 table

enter image description here

Disease table

enter image description here

Combination table

enter image description here

• Engo: I tried to came up with a better title, but I found it hard to find an and appropriate and explanatory title.
• tim: It looks like you are using PHP. It's not clear if you have any protection against SQL injection, but especially for a medical db it would be best to use the recommended protection, ie prepared statements. Escaping may be secure, or it may not be, and it is definitely a lot easier to screw up and a lot harder to analyze than prepared statements.
• Engo: Yeah I am using PHP, but fortunately the security is not an issue. Its all about the database side, because I am new in this area ;)

Answer 1

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 

• Engo: Thank you for your answer! The purpose of the combination table is to list diseases ordered by their weight (d_weight), based on a combination of user input (age (age), gender (gender), body part (bp_id), main symptom (s_id) and symptoms that apply (s_apply)). I am not sure how to design a new table for s_apply, because I don't know what to do with records that have an empty s_apply.
• janos: I figured that much. My point was that the name "combination" fails to convey it's purpose, which is not good. As for the new table, there will be no records for cases with no additional symptoms, but there is nothing wrong with that, is there?
• Engo: It's not clear to me how a combination will look like when a record has 2 symptoms in s_apply. Could you explain this please? What do you think is a better name for the combination table? "case"?
• janos: Yes, case sounds better. If you create an additional table, then the case table won't need an s_apply column. That will be in the new table. In that new table, there will be 0 or more records for each case.