Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2013
    Posts
    7

    Should I split an entity into two entities?

    Hi everyone,

    First i would like to say that by posting this i am not asking anyone to do my work for me as i am always proud and gain much self satisfaction in completing my own work, but i need some advice and guidance from a second busy working mind

    I am designing an ERD for a new system at a Hospital. Of course one of the entities will be

    'Patient'

    It is specified each patient will be receiving either treatment of medication or operation, therefore i want to ask whether to split this entity in to two, such as

    'Patient Operation'

    and

    'Patient Medication'

    The reason i am doing this is because by splitting these in to two categories the retrival time of infomration will be of a higher speed? Am i correct with this? If not, or have any other advice all will be helpfull, thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    whether it will make a difference in performance or not I dunno (Is uspect if there is any difference its going to be negliagble.

    however I suspect there is a functional reason to split the entities. I could see a sub/supertype approach if required with treatment being the sub (master) type and operation, prescription and other being the super (child) type
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2013
    Posts
    7

    Are my Tables efficient and designed well...

    Hi everyone, need some advice on whether the way i decided to split my tables up are correct.

    I have a treatment table as seen below,

    Treatment Table

    TreatmentID -TypeOfTreatment - dateOfTreatment - PastMedIssues - patientName - patientID
    1 - - - - - - - Op - - - - - - - 11 01 13 - - - - - - -sclerosis- - - - - -Joe - - - - - - 1
    1 - - - - - - - Med - - - - - - - 11 01 13 - - - - - - -sclerosis- - - - - - Joe- - - - - - 1
    2- - - - - - - Med - - - - - - -01 01 13- - - - - - - Heart - - - - - - - Mindy - - - - - - 2
    3- - - - - - - Op - - - - - - - 23 06 13- - - - - - -Lung- - - - - - - Mike - - - - - - - 4
    3 - - - - - - -Med- - - - - - - 23 06 13- - - - - - -Lung- - - - - - - Mike - - - - - - - 4

    However, as you can see their is data repeating, so i decided the following,




    OperationTreatment Table

    OpTreatmentID - patientID - TypeOfOperation - DateofOperation - pastMedIssues

    1 - - - - - - -- 1 - - - - - Heart - - - - - - -11 01 13 - - - - - - -sclerosis


    AND


    MedicationTreatment Table

    MedTreatment - pateintID - TypeOfMedication - DatePrescribed - pastMedIssues

    1- - - - - - - 1 - - - - - - -ibuprofen- - - - - - -11 01 13- - - - - - -sclerosis




    So i made a Medication Treatment table and a Operation Treatment Table to avoid the duplicated data from the first table. Is this efficient, is their any advice on improvement....

    But i am wondering when i come to secify the OpTreatmntID or MedTreatmentID in other tables such as Patient, what if that patient only had medication, the OpTreatment field will be left as Null, any advice......Please help, really will be apppreciated

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    There is still repeating data.
    Scelerosis is a diagnosis and not a function of treatment, either surgical or medication
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2013
    Posts
    7
    Quote Originally Posted by healdem View Post
    There is still repeating data.
    Scelerosis is a diagnosis and not a function of treatment, either surgical or medication
    The keywords dont really matter, their just an example not a medical student. Also what data is still repeating can you be tad more clear please..... Thanks

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    At least in my experience, there isn't a one-to-one correlation between patient and treatment. One patient tends to receive many different kinds of treatment, often in the same event (hospital stay). They frequently have multiple treatments of the same kind (I've personally had multiple operations and multiple medications on a single day).

    I believe that you need multiple entities, with a many-to-many joining table between patient and all of the others that you've cited so far.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    See my answer to your previous post, then spend a bit of time with Marc Rettig's Normalization Poster. This is a deceptively simple poster sized layout that is both attractive and wildly informative... It lays out the basic principles of what is needed to normalize 99.9% of the databases I've ever seen.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2013
    Posts
    7

    Joining the two tables efficiently through a third table

    Hi everyone,

    I am quite mind boggled right now, I want to create a table called 'treatment' however it is possible for the treatment to either be a medication treatment or operation treatment.

    In order to display this in the table means there will be repeating data, as you can see below

    Treatment Table

    TreatmentID -TypeOfTreatment - dateOfTreatment - PastMedIssues - patientName - patientID
    1 - - - - - - - Op - - - - - - - 11 01 13 - - - - - - -sclerosis- - - - - -Joe - - - - - - 1
    1 - - - - - - - Med - - - - - - - 11 01 13 - - - - - - -sclerosis- - - - - - Joe- - - - - - 1
    2- - - - - - - Med - - - - - - -01 01 13- - - - - - - Heart - - - - - - - Mindy - - - - - - 2
    3- - - - - - - Op - - - - - - - 23 06 13- - - - - - -Lung- - - - - - - Mike - - - - - - - 4
    3 - - - - - - -Med- - - - - - - 23 06 13- - - - - - -Lung- - - - - - - Mike - - - - - - - 4



    So, i decided the following, as you can see below,



    OperationTreatment Table

    OpTreatmentID - patientID - TypeOfOperation - DateofOperation

    1 - - - - - - -- 1 - - - - - Heart - - - - - - -11 01 13


    AND


    MedicationTreatment Table

    MedTreatment - pateintID - TypeOfMedication - DatePrescribed

    1- - - - - - - 1 - - - - - - -ibuprofen- - - - - - -11 01 13
    2 - - - - - - - 2- - - - - - -paracetamol- - - - - - -14 05 13




    But now the problem is how do i connect the 'OperationTreatment' Table and the 'MedicationTreatment' Table??

    Do i add a TreatmentID in both the tables and create a third table that joins these two together??

    I tried however, when TreatmentID 1 only received MedTreatment and no OpTreatment, that will mean a Null will be remaining in the OpTreatment ID collumn, as you can see below


    Treatment Table (Connect other two)

    Treatment ID MedTreatmentID OpTreatmentID
    1 ..................... 1 ........................... 1 ........
    2.......................2......................... ... NULL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Since all three of your message threads deal with a single topic, I've merged them so that it is more likely that you'll get answers that consider all of your questions/issues.

    My take on the subject remains the same because I've read all of your posts/questions. I think that you need to consider a more generalized design that allows for multiple treatments (medications, surgeries, etc.) for a single event (visit).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Treatment Table

    TreatmentID -TypeOfTreatment - dateOfTreatment - PastMedIssues - patientName - patientID
    1 - - - - - - - Op - - - - - - - 11 01 13 - - - - - - -sclerosis- - - - - -Joe - - - - - - 1
    1 - - - - - - - Med - - - - - - - 11 01 13 - - - - - - -sclerosis- - - - - - Joe- - - - - - 1
    2- - - - - - - Med - - - - - - -01 01 13- - - - - - - Heart - - - - - - - Mindy - - - - - - 2
    3- - - - - - - Op - - - - - - - 23 06 13- - - - - - -Lung- - - - - - - Mike - - - - - - - 4
    3 - - - - - - -Med- - - - - - - 23 06 13- - - - - - -Lung- - - - - - - Mike - - - - - - - 4



    So, i decided the following, as you can see below,



    OperationTreatment Table

    OpTreatmentID - patientID - TypeOfOperation - DateofOperation

    1 - - - - - - -- 1 - - - - - Heart - - - - - - -11 01 13


    AND


    MedicationTreatment Table

    MedTreatment - pateintID - TypeOfMedication - DatePrescribed

    1- - - - - - - 1 - - - - - - -ibuprofen- - - - - - -11 01 13
    2 - - - - - - - 2- - - - - - -paracetamol- - - - - - -14 05 13
    I want to understand this issue(because it looks something interesting for me),
    but I puzzeled the sample data provided OP, and I felt difficulty to undersand the issue definitely.


    Some points I couldn't understand were...
    (1) How could arrive the data in OperationTreatment Table from Treatment Table?
    Because,
    No " TypeOfOperation = 'Heart' " was in Treatment Table.
    (" PastMedIssues = 'Heart' " was in Treatment Table.)
    No value of 'Heart' corresponding to "PatienID = 1" nor "TreatmentID = 1" was in Treatment Table.
    OperationTreatment Table

    OpTreatmentID - patientID - TypeOfOperation - DateofOperation

    1 - - - - - - -- 1 - - - - - Heart - - - - - - -11 01 13
    (2) No 'ibuprofen' nor 'paracetamol' was in Treatment Table,
    but suddenly appeared in MedicationTreatment Table.

  11. #11
    Join Date
    Oct 2013
    Posts
    7
    1. The first table I made ‘Treatment Table’ was my first solution, I had the attention of keeping that as table for treatment. However, as it doesent correspond with 1NF or 2NF I had to change it, so I made the tables ‘MedicationTreatmen’ and ‘OperationTreatment’. The first table is just the first draft of the solution to making the ‘Treatment’ Table. Also, the ‘Treatment table’ that I am to use is the last tabl I displayed.


    2. The tables ‘OperationTreatment’ and ‘MedicationTreatment’ are the solution to fixing the first table (‘Treatment’ Table) so that table is out of the equation now.


    OperationTreatment Table

    OpTreatmentID - patientID - TypeOfOperation - DateofOperation

    1 - - - - - - -- 1 - - - - - Heart - - - - - - -11 01 13


    AND


    MedicationTreatment Table

    MedTreatment - pateintID - TypeOfMedication - DatePrescribed

    1- - - - - - - 1 - - - - - - -ibuprofen- - - - - - -11 01 13
    2 - - - - - - - 2- - - - - - -paracetamol- - - - - - -14 05 13




    3. Now with the two new tables, I need to find a way to join them, so that the type of medication, and the type of operation to one patient show in one table. However, the way I am doing this, as seen below…. Even though i didn't add TreatmentID in the above two tables which as you pointed out, i must, thanks


    Treatment ID MedTreatmentID OpTreatmentID PatientID
    1 ..................... 1 ....................... 1 ............1
    2.......................2......................NUL L...........3

    But this solution now gives me a NULL, as imagine treatmentID 2 only received medication and no treatment, that means a NULL will remain, what is the right ay to fix this as i am very confused, thanks for your help

    Also Pat, thanks you giving your time, much appreciated

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    type of operation should be a FK to an operation types table (storing text in a row thats repeating data) its asking for trouble once you hit the jargon rich world of medics)
    likewise type of medication should be a FK to a treatment type table
    a patient can have more than one pre existing condition/diagnosis
    a patient may have a diagnosis that is untreatable (too sever or two minor)
    a patient may have multiple prescriptions at the same time
    they may also have multiple presecriptions covering one or more diagnoses (say they may be on statins AND warfarin AND Insulin AND anti-Hypertension)
    how do you handle prescriptions such as the varying levels that patients with blood clotting issues handle the warfarin treatement (it may be prescribed as, say 35mg daily, but comprised of 3 x 10mg & 1 5mg
    you should be moving the model beyond 1 or 2NF, they are waypoints on the road to 3NF or higher
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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