none
How to implement a table that are related to two other tables that have a (one-to-many relation between them)

    Question

  • Hi all i have the following problem while designing my sql server 2008 database for my asp.net mvc 3:-

    i have the folloiwng two tables:

    1. medicine :- with the following fields;

    • medicineID (PK)
    • Name
    • type

    2. Doze

    • DozeID (PK)
    • medicineID (FK to the medicine table)
    • description

    Now i have a Patient_Visit table which should reference to both a medicine and the doze taken, i am currently incliding both the DozeID and medicineID in the Patient_Visit table as FKs.

    But the issue is that if i know the DozeID then i will know also the MedicineID without the need to store the MedicieID in the Patient_Visit table,,, so should i only include the DozeID in the Patient_Visit table - although it will not make sense to have a DozeID in the Patient_Visit table without storing the medicieID - !! ?

    BR



    • Edited by johnjohn123 Wednesday, March 28, 2012 2:22 AM
    Wednesday, March 28, 2012 2:20 AM

Answers

  • I think I would only keep a DozeID column in the PatientVisit table if it is a guaranteed 1 to 1 relationship. If there is any possibility that a patient could be given two or more medicines in a single visit, then I would want a relationship table to map a visit to possibly multiple dozes. I think that is what Uri and Mohamed were getting at in their answers. I would probably create the relationship table even if the possibilities are limited to a patient getting 0 or 1 medications per visit. If you leave DozeID as an FK in Visit, then you have to worry about whether you use a null to represent a visit where a patient got no medicines. If you have a VisitDoze relationship table, then you just don't insert a row if a patient gets no medicine on a visit.

    VisitDoze

    • VisitID - FK to PatientVisit
    • DozeID - FK to Doze
    • Composite PK of VisitID, DozeID

    One warning; I think all of us posting answers are making an assumption about the relationship between Doze and Medicine because there is some contradictory information about that relationship in the first post. The table structure you outlined for Doze, with DozeID as a single column PK and MedicineID as an FK works if there is a 1 to many relationship between Doze and Medicine, that any given DozeID will always map back to only one MedicineID. If that is true, then anything only needs a DozeID FK back to the Doze table, from Doze you could do a join back to Medicine based on MedicineID. But later in the post you say that putting in a DozeID without a MedicineID as FK's in PatientVisit makes no sense. To me, that would be true only if there is a more complex relationship, like a many to many relationship, between Doze and Medicine.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Saturday, March 31, 2012 8:39 PM
  • So I am going to assume that Doze is the application of medicine (which in English is spelled Dose, so I might be misinterpreting :))

    But, assuming that, I think I agree with Alan, and less Uri, though his solution makes sense as well (I don't exactly know what the Dose would represent in his key here though: (VisitID,medicineID,DozeID ).  The problem with your question, and almost all design questions is lack of requirements. What kind of doctor are we talking? A hospital where the patient may stay a week?  Or a clinic where the doctor is required to only give a single dose of medicine per visit?

    My guess design, based solely on assuming the former (and it would be more flexible to allow for multiple doses in the structure, even if the current rules state otherwise) would be:

    Patient -- A person who is getting treated
    Medicine -- Domain of medicines available to be administered to a patient in the hospital
    PatientVisit -- An instance of the patient being treated Attributes woiudl include time of stay, type of stay, admitted status, etc
    PatentVisitDose --An application of medicine to the patient during the visit. Attributes would include medicine, amount, and time

    The relationships would be (with <-- pointing to the parent)

    Patient <-- PatientVisit
    PatientVisit <-- PatientVisitDose
    Medicine <-- PatientVisitDose

    You would not want to have the Medicine in both Dose table and the PatentVisit table, nor in a PatientMedicine table (since the cardinality of medicine to patient is likely greater than one - to - one, like Alan was mentioning), because that would mean the data was repeated and you would then have two ways that you were representing one fact which (like the person with two watches), often will get out of sync.

    So, yes, that is my long way of agreeing with you that you don't need to double up the representation of the medicine at all.


    Louis

    Monday, April 02, 2012 2:17 AM
    Moderator
  • Since Doze includes Medicine, anytime DozeId is used, the Medicine is already known, as you explained. Therefore, when the Patient_Visit TABLE includes DozeId, medicine is inferred. Including medicineID in the Patient_Visit TABLE would be redundant, therefore.

    Wednesday, March 28, 2012 1:13 PM
    Answerer

All replies

  • >>>so should i only include the DozeID in the Patient_Visit table

    Probably but another scenario could  be  having a dedicated table which contains three columns (VisitID,medicineID,DozeID ) and remove medicineID  from the Doze table....


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, March 28, 2012 6:55 AM
    Moderator
  • The same as Uri Dimant told you "create a table which contains three columns (VisitID, medicineID, DozeID)"

    but remain the medicineID in the Doze table because you will need it in any other operations


    Thanks,
    MOHAMED A. SAKR | Software Development Lead Engineer | EgyptNetwork
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Also try to Vote as Helpful

    • Proposed as answer by MohamedSakr Wednesday, March 28, 2012 9:26 AM
    • Unproposed as answer by Brian TkatchEditor Wednesday, March 28, 2012 1:08 PM
    Wednesday, March 28, 2012 9:26 AM
  • Since Doze includes Medicine, anytime DozeId is used, the Medicine is already known, as you explained. Therefore, when the Patient_Visit TABLE includes DozeId, medicine is inferred. Including medicineID in the Patient_Visit TABLE would be redundant, therefore.

    Wednesday, March 28, 2012 1:13 PM
    Answerer
  • thanks for the reply; but i can not understand what will be the benefit of providing a seperate table for storing the (VisitID, medicineID, DozeID).

    BR

    Wednesday, March 28, 2012 1:21 PM
  • I dont think storing them in separate table will help you VisitID, medicineID, DozeID. Are you intrested in storing their mappings?
    Saturday, March 31, 2012 10:01 AM
  • I think I would only keep a DozeID column in the PatientVisit table if it is a guaranteed 1 to 1 relationship. If there is any possibility that a patient could be given two or more medicines in a single visit, then I would want a relationship table to map a visit to possibly multiple dozes. I think that is what Uri and Mohamed were getting at in their answers. I would probably create the relationship table even if the possibilities are limited to a patient getting 0 or 1 medications per visit. If you leave DozeID as an FK in Visit, then you have to worry about whether you use a null to represent a visit where a patient got no medicines. If you have a VisitDoze relationship table, then you just don't insert a row if a patient gets no medicine on a visit.

    VisitDoze

    • VisitID - FK to PatientVisit
    • DozeID - FK to Doze
    • Composite PK of VisitID, DozeID

    One warning; I think all of us posting answers are making an assumption about the relationship between Doze and Medicine because there is some contradictory information about that relationship in the first post. The table structure you outlined for Doze, with DozeID as a single column PK and MedicineID as an FK works if there is a 1 to many relationship between Doze and Medicine, that any given DozeID will always map back to only one MedicineID. If that is true, then anything only needs a DozeID FK back to the Doze table, from Doze you could do a join back to Medicine based on MedicineID. But later in the post you say that putting in a DozeID without a MedicineID as FK's in PatientVisit makes no sense. To me, that would be true only if there is a more complex relationship, like a many to many relationship, between Doze and Medicine.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Saturday, March 31, 2012 8:39 PM
  • So I am going to assume that Doze is the application of medicine (which in English is spelled Dose, so I might be misinterpreting :))

    But, assuming that, I think I agree with Alan, and less Uri, though his solution makes sense as well (I don't exactly know what the Dose would represent in his key here though: (VisitID,medicineID,DozeID ).  The problem with your question, and almost all design questions is lack of requirements. What kind of doctor are we talking? A hospital where the patient may stay a week?  Or a clinic where the doctor is required to only give a single dose of medicine per visit?

    My guess design, based solely on assuming the former (and it would be more flexible to allow for multiple doses in the structure, even if the current rules state otherwise) would be:

    Patient -- A person who is getting treated
    Medicine -- Domain of medicines available to be administered to a patient in the hospital
    PatientVisit -- An instance of the patient being treated Attributes woiudl include time of stay, type of stay, admitted status, etc
    PatentVisitDose --An application of medicine to the patient during the visit. Attributes would include medicine, amount, and time

    The relationships would be (with <-- pointing to the parent)

    Patient <-- PatientVisit
    PatientVisit <-- PatientVisitDose
    Medicine <-- PatientVisitDose

    You would not want to have the Medicine in both Dose table and the PatentVisit table, nor in a PatientMedicine table (since the cardinality of medicine to patient is likely greater than one - to - one, like Alan was mentioning), because that would mean the data was repeated and you would then have two ways that you were representing one fact which (like the person with two watches), often will get out of sync.

    So, yes, that is my long way of agreeing with you that you don't need to double up the representation of the medicine at all.


    Louis

    Monday, April 02, 2012 2:17 AM
    Moderator
  • Hi John,

    I dont think that in your case third reference table is required yes it can be help full if you want to store default doze quantity for medicines for doctor referenec.

    other wise what is been suggested is correct.

    Hope it will help you.


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    Thursday, April 05, 2012 9:05 AM