Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    5

    Unanswered: Help in Database design

    Hi All,
    I am working on Access 2k- sql server 2k project. For example I have the following 2 tables
    Table 1:
    AllergyId
    AllergyName

    Data in Table 1
    AllegyId AllergyName
    1 AllergyId_1
    2 AllergyId_2
    3 AllergyId_3


    Table 2: (Main table)
    PatientId
    AllergyId
    Comments

    Data in table 2 is:
    PatentId allergyId Comments
    1 AllergyId_1 Comments_1
    2 AllergyId_2 Comments_2
    3 AllergyId_3 Comments_3


    Table3:
    PatientId
    PatientName

    Table 2 is normalised but I need to enter data in table 2 form using the format below:

    ----------------------------------------------------------------
    PatientName AllergyId_1 AllergyId_2 AllergyId_3 ...
    ----------------------------------------------------------------
    Test Comments_1 Comments_2 Comments_3

    I do not know the max. number of AllergyIds so how should I proceed?

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    Isn't this a many to many relationship patient to Allergy? Tht is many patients can have many allergies, some will only have one.

    This would look something like:

    AllergyID
    AllergyName

    PatientID
    PatientName
    Comments

    ReactionID
    PatientID
    AllergyID
    AllergyDate


    HTH

  3. #3
    Join Date
    Aug 2002
    Posts
    78
    OK newbie here. . but do have comment/question. Even though many patients can have many alergies. . wouldn't it be a one to many relationship because you are only reporting for the one specific patient with (one or many) alergies at a time?

    Originally posted by dynamictiger
    Isn't this a many to many relationship patient to Allergy? Tht is many patients can have many allergies, some will only have one.

    This would look something like:

    AllergyID
    AllergyName

    PatientID
    PatientName
    Comments

    ReactionID
    PatientID
    AllergyID
    AllergyDate


    HTH

  4. #4
    Join Date
    Feb 2002
    Posts
    403
    In the structure i posted you are correct.

    In the original structure the relationship was a many to many. You cannot create a many to many so we create a bridging table. In your example

    AllergyID
    PatientID

  5. #5
    Join Date
    Oct 2002
    Posts
    5
    Thanks for your replies. Now after I create the tables as you have mentioned I want to create a continuous forms in access like this
    Header: Patient Name | AllergyName_1| AllergyName_2|...
    Data: | AllergyDate_1 | AllergyDate_2 |...

    I appreciate you help.

  6. #6
    Join Date
    Feb 2002
    Posts
    403
    I think a subform approach may work better for you. Create a subform for the AllergyID/PatientID table, and present this on the form for the patient details. In this way you could, for example attach a photo of the allergic reaction, or other patient data and present great information to your user.

    It is upto you, and knowing what the data is used for will determine your approach.

  7. #7
    Join Date
    Oct 2002
    Posts
    48

    I agree with dynamictiger

    Original design was good.

    Just take care while making entry screens.
    sub report is good for entering patients's allergies.

    I think 3 screens will be there.

    1. Patient Master
    2. allergy Master
    3. Patient's Allergy.

    In 3rd screen, Patient's Id and comments will be there. while patient's allergy will be at sub screen of that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •