#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    13
    Rep Power
    0

    Storing medical ailments in a database Databases


    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. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by nivashni1234
    Does this mean I will have to create a field for each ailment in my database ?
    no, a row for each one in an Ailments table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    13
    Rep Power
    0
    Originally Posted by r937
    no, a row for each one in an Ailments table
    Thank Rudy

    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

    Code:
    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



    Code:
    AILMENT_ID  AILMENT_NAME    
    
    33  Diabetes    
    55  Hypertension    
    77  Cancer  
    87  Thyroid 
    99  cardiomyopathy  
    201 Migrane 
    202 Sinus   
    295 Psoriasis   
    299 Arthrithis
    ASSESSMENT-BMI


    Code:
    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.

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

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

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

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

    Thanks Kimmy
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,953
    Rep Power
    381
    In each case, what would the desired result look like?

IMN logo majestic logo threadwatch logo seochat tools logo