I am trying to develop a medical symptom checker app and therefore I need to convert my excel datasheet which contains over 190k of records into a MySQL database. I have asked and read multiple related questions before, but I still find it difficult to create an efficient/proper database design.
Please take a look at the design of the app (1st image) to get an idea how the app works.
Steps user should follow to check symptom(s)
Disease table is fairly simple; it contains columns
H..O, with duplicates removed. Plus there is a unique ID for each row. (See
AUTO_INCREMENT) I'm unclear on whether
disease_weight belongs in the Disease table or somewhere else.
Symptoms might be best implemented as a
Another table contains columns of gender, age_range, body_part, symptoms, and disease_id (and maybe disease_weight).
SELECTs that I see are
SELECT symptoms FROM table2 WHERE age_range = $ar AND gender = $gender AND body_part = $bp AND FIND_IN_SET(symptoms, $symptom1);
To get the possible secondary symptoms.
(You have not explained how a user will enter an age_range; I assume that will end up in your cgi language as
SELECT d.name, ... FROM Table2 t JOIN Diseases d ON d.disease_id = t.disease_id WHERE age_range ... AND symptoms & $symptoms;
(I may have a syntax error on the
If there are other
SELECTs, you need to think about them now, not later.
You have not explained how this dataset will be updated; that could be an issue, too.
You did not actually ask how to get from Excel to MySQL; let's finish the database design first.