Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    1

    Question Unanswered: Please help with database design

    I would like to create a relationship between patient and allergen table. Each patient can have many allergies and each allergy can belongs to many patients.

    Table tblPatient have primary key(PatientID) which is already link to a different table with a 1 to many relationship.

    Please help. Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: Please help with database design

    Create an allergy table... to list all allergies with a primary key say Allergy_ID. Create a intermediate/composite table. Call this PatientAllergies. It will need its won PK (even if it is only an AutoNumber) say PatientAllergy_ID. Then add the Patient table and allergy table Primary keys into the table and create a one to many from Patient to PatientAllergy and one to Many from Allergy To PatientAllergy ...

    Hope this helps...

    Originally posted by isdex1
    I would like to create a relationship between patient and allergen table. Each patient can have many allergies and each allergy can belongs to many patients.

    Table tblPatient have primary key(PatientID) which is already link to a different table with a 1 to many relationship.

    Please help. Thanks.
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    On the whole I agree with you Mona
    3 tables, 1 patent, 1 Allergies and 1 with keys from patent and allergies

    As for the linked tables key the best option should be a composite key that covers both of the foreign keys in the design view this is done by highlighting both rows and then clicking the primary key assignment button
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Or... indexes

    Or, using the table as Mona suggested, leave the primary key as PatientAllergin_Id, and add a composite unique index to the foreign keys of Patient_Id, Allergin_Id.

  5. #5
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: Or... indexes

    It is Friday, late in the afternoon and the beers are starting to beckon ... so I will make thi short. I suggested a dummy PK for the Patient_allergy table as I was not sure if you were intending to list all allergies that a patient has or allergies that a patient is having at a particular time ... If the latter were true (as in my case since I suffer hayfever once a month) you may record Mona, HayFever about 12 times in one year ... just on different occurances ...

    If you are merely recoding possible allergies that a patient has then timoney is right, composite keys ....


    Hope this makes sense ...

    The beer is calling .....

    Have a good weekend

    Originally posted by acg_ray
    Or, using the table as Mona suggested, leave the primary key as PatientAllergin_Id, and add a composite unique index to the foreign keys of Patient_Id, Allergin_Id.
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i have to disagree as your allergy is to pollen, and that doesn't change, you don't suddenly once a month become allergic to it, you always are what happens is about once a month you suffer an allergic reaction (known as hayfever due to the fact that poeple suffered from is mainly during the hay making season (ie lots of grass pollen in the air))

    Someone whoes allergic to peanut oil does stop being allergic just because he isn't in anapalactic shock, your confusing symptoms with the illness
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    I am actually not confusing symptom with illness, I was not sure of the application of the database, it may a record of each visit a patient makes.

    And I apologise if I have offended you by my comments as your response seemed a little harsh as I agreed with you in the first place.

    Originally posted by m.timoney
    i have to disagree as your allergy is to pollen, and that doesn't change, you don't suddenly once a month become allergic to it, you always are what happens is about once a month you suffer an allergic reaction (known as hayfever due to the fact that poeple suffered from is mainly during the hay making season (ie lots of grass pollen in the air))

    Someone whoes allergic to peanut oil does stop being allergic just because he isn't in anapalactic shock, your confusing symptoms with the illness
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    my turn to apologise, no offence was take and non was intended, i was just stating my intepretation of how they specified the problem and pointing out what i thought the was the source in the difference of opion.

    as it turns out i was wrong, from your post i gather i was dealing with what was said and you were dealing with that and what they might of ment
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    To be honest any other day I would laughed it off as a difference in opinion of two developers and make it a good learning experience. Yesterday, I got way baddddddddddd personal news and your reply wwas the first I saw, and I think I lashed out unwarranted ..

    I apologise ...



    Originally posted by m.timoney
    my turn to apologise, no offence was take and non was intended, i was just stating my intepretation of how they specified the problem and pointing out what i thought the was the source in the difference of opion.

    as it turns out i was wrong, from your post i gather i was dealing with what was said and you were dealing with that and what they might of ment
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

Posting Permissions

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