database design for a symptom checker application

Question

I am trying to create a symptom checker similar to WebMD where user inputs their symptoms and based on the input it suggests the disease. I have two tables, one containing all the symptoms and the other containing all the diseases. But I have no idea how to associate these two tables. I can go for the following approach;

+-----------------+----------+----------+----------+--------+----------+
|     Disease     | Symptom1 | Symptom2 | Symptom3 | Gender | AgeGroup |
+-----------------+----------+----------+----------+--------+----------+
| Some Disease    | Yes      | No       | Yes      | All    | 19-35    |
| Another Disease | No       | Yes      | Yes      | Male   | All      |
+-----------------+----------+----------+----------+--------+----------+

But it is redundant as I have to include all the records in the "Symptom" table as a column in this table and There will be issues when I have to add a new symptom. So is there any better way to organize this? Or is there any better alternative approach (third party APIs?) to achieve this?

score 3 · Accepted Answer · answered Apr 16 '14 at 12:53

It would be much better to have three tables: diseases, symptoms and "dissyms". The first two are simple tables comprising of primary key and name (the diseases table might possibly have one or two extra fields such as you show above), whereas the third table is a 'join table' which links the two other tables. This table would consist solely of primary key to diseases table and primary key to symptoms table, although again, it would be possible to add an extra field which would provide further information.

I was recently ill with pneumonia so I know the symptoms. The 'diseases' table would have a tuple for pneumonia; the 'symptoms' table would have tuples like 'fever', 'dry cough', 'wet cough', 'sounds in lungs', etc, and the 'dissyms' table would have tuples linking those symptoms to pneumonia.

You will have problems with your table structure when you want to add another symptom. What I have described is the canonical method of handling the problem.

database design for a symptom checker application

1 Answers1