Storing medical ailments in a database

Databases
1

Hello guys

I am trying to find the best method to store medical ailments , like Diabetes, Hypertension, Cancer etc in a
database.

My form will list over 200 ailments and the user will check those that affect him. I could serialize those
selected and then store it in a database but I want to be able to use the information at a later stage.
For example I want to know what ailments are selected with Diabetes and Hypertension.
Does this mean I will have to create a field for each ailment in my database ?

I am confused here.

I would appreciate any assistance.

Thanks
Kimmy

2

Hi pandayk welcome to the forum

IMHO serializing data so it’s like “a table inside a field” is a poor approach. Especially if you need to work with it later.

It can be done, but it can result in needing a lot of complex code to do what would be easier and more efficient if the database had a better architecture.

3

no, a row for each one in an Ailments table

4

Thanks, I got this right. I don’t know what I was thinking by asking this in the first place.

I am implementing a project and 3 tables form the basis of my report, I have other tables but they are not part of the report.

The 3 tables are ASSESSMENT-AILMENT AND AILMENT AND ASSESSMENT-BMI

ASSESSMENT-AILMENT

ASSESSMENT_ID AILMENT_ID STATUS

100 201 MAJOR
100 202 MAJOR
100 295 MAJOR
100 299 MINOR
100 55  MINOR
100 99  MINOR
100 77  MAJOR
100 87  MAJOR
100 33  MINOR
101 55  MAJOR
101 99  MAJOR
101 77  MAJOR
101 87  MINOR
101 201 MAJOR
101 299 MAJOR
101 202 MINOR
101 295 MINOR
102 55  MAJOR
102 99  MAJOR
102 295 MINOR
102 87  MAJOR
102 201 MINOR
102 202 MINOR
102 299 MAJOR

AILMENT


AILMENT_ID  AILMENT_NAME    

33  Diabetes    
55  Hypertension    
77  Cancer  
87  Thyroid 
99  cardiomyopathy  
201 Migrane 
202 Sinus   
295 Psoriasis   
299 Arthrithis

ASSESSMENT-BMI

ASSESSMENT_ID   BMI
100 33
101 23
102 14

I am looking of ideas on the type of queries I can run on these 3 tables. So far I can think of the following.

Which of the ailments are mostly tagged as MAJOR. Which are the 5 most MAJOR ailments What ailment appears most with another. When one ailment is a MAJOR, what is the MINOR (highest)

The number of ailments could be up to to 200 and the number of assessments done could be up to 20 000

I want to be able get the following answers, I am wondering if it it possible in SQL.

  1. If an ailment is MAJOR, eg Diabetes, what other ailments where also selected as Major.

2.If an ailment is MAJOR, eg Diabetes, what other ailments where also selected as MInor.

  1. How many times was a ailment selected a Major and also MInor.

  2. If the BMI was less than a value x, what where the ailments selected.

  3. What is the relationship between 2 ailments, eg, Diabetes and Hypertension, how many times do they appear together

I am no sure how to implement these, but would appreciate some ideas please.

Thanks Kimmy

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.