CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
Engo
Engo

Reputation: 969

Converting medical datasheet into MySQL database design

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)

  1. User chooses gender, age and bodypart
  2. App shows all (common/less common) symptoms of chosen bodypart
  3. User chooses symptom
  4. Apps asks if there are more symptoms that apply (only if symptom has additional symptoms in database). User can tick up to 2 additional symptoms.
  5. App shows all (common/less common) diseases of chosen symptom and additional symptoms. The order (weight) of the diseases is dependent on the selected age, gender, bodypart, main symptom and selected additional symptoms.
  6. User chooses disease
  7. App shows disease information

Attributes: age, gender, bodypart, symptom, disease

I already have a database which contains all data and possible combinations of input/output. Unfortunately it is not designed to be used in an app (2nd image). As you can see in the 2nd image, the order of the diseases (disease weight) is dependent on the selected age, gender, bodypart, symptom and selected additional symptoms (additional symptoms that apply). Each symptom may have up to 2 additional symptoms. The user can check either 0, 1 or 2 of the additional symptoms and the order of the disease will be different for each of these options.

Each symptom is either common (1) or less common (0). It depends on the user input (age, gender, bodypart).

Each disease weight <= 5 is considered to be a common disease. Diseases with weight > 5 are considered as less common diseases. Of course it is also depends on the user input (age, gender, bodypart, symptom, additional symptoms). I have tried so many things but I still don't know how to design this feature in a proper way.

Could anyone help me designing a suitable database?

UPDATE 1

Basically we need to keep 3 queries in mind when designing the database

  1. Get all symptoms (symptom.id, symptom.name, symptom.critical, symptom community (common/less common)) that belong to the combination of selected age = $age, gender = $gender AND bodypart = $bp
  2. Get all additional symptoms (symtpom.id symptom.name) of selected symptom
  3. Get all diseases (disease.id, disease.name, disease.critical disease weight) that belong to the combination of selected age, gender, bodypart, main symptom and additional symptoms.

App design app design

Excel datasheet excel datasheet

Upvotes: 1

Views: 708

Answers (2)

Hafizudin Johari
Hafizudin Johari

Reputation: 63

I know this question is outdated. But from my research, a lot of symptoms checker application are using API to access certain data. From here, is one of the API used. Not sure if you are used your own created database but that is bad practice because the infromation that you used might be wrong and outdated.

Upvotes: 1

Rick James
Rick James

Reputation: 142298

A 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 SET datatype.

Another table contains columns of gender, age_range, body_part, symptoms, and disease_id (and maybe disease_weight).

The main 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 $ar. (Etc)

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 SET operators.)

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.

Upvotes: 2

Related Questions