Question

  • Creator
    Topic
  • #2255686

    Help with normalisation

    Locked

    by jai_comp ·

    Hello,

    I am a medic and trying a develop a small database for our clinic. The purpose of the design is to accept pathology test request from patients and pass these details to labs who will update the status and results accordingly. Please let me know if the following tables are normalised.

    TableName: PatientDetails
    patientRef
    surname
    firstname
    dob
    address1
    address2
    address3
    postcode
    consentpost
    mobile
    consentMobile
    telephone
    consentTelephone
    ethnicity
    sex

    TableName: Test
    testID
    patientRef
    staffID
    screeningSitecode
    testDateRequested

    TableName: TestDetails
    testID
    patientRef
    reasonForTest
    SpecimenType
    testResults
    testStatus
    stisymptoms
    testResultsdateCompleted

    Tablename: Staff
    staffID
    Staffname
    staffdepartment
    staffrole
    staffloginID
    staffpassword
    access_level

    Table – ScreeningLocation
    screeningSitecode
    screeningSiteName

    Table – Drugs
    drugID
    drugName
    Dose

    Table – Partner
    partnerID
    patientRef
    partnername
    partnerphonenumber

    Table: Drug Prescription
    drugID
    testID
    patientRef

    Many thanks

    steven

All Answers

  • Author
    Replies
    • #2520649

      Clarifications

      by jai_comp ·

      In reply to Help with normalisation

      Clarifications

    • #2520487

      Could you explain the table relations a little?

      by locolobo ·

      In reply to Help with normalisation

      I don’t fully understand the data structure. Could you explain the table relations? For instance the Drug Prescription table has a link to TestID. I’m not sure why.

      This is just a suggestion but you might want a sample ID. Most labs receive a sample (blood, urine, tissue, etc) and then perform test(s) on it. They then send you a report for that sample. You could have multiple samples per patient with multiple tests per sample.

      As far as normalisation goes, it looks good. Are you allowing for more than one partner per patient?

      • #2520368

        Thank you –

        by jai_comp ·

        In reply to Could you explain the table relations a little?

        Thank you locolobo and damian.

        Damian – I was just thinking do I really need to split Test table into two tables
        (Test and TestDetails) as there relation seems to be one-one (I think).
        I don’t see why they wouldn’t comply with the 2nd normal form of avoiding partial redundancies
        if put together.
        for example, testResults, SpecimenType, stisymptoms, testResultsdateCompleted are all
        dependant on both primary keys testId and patientRef? Am I thinking this right or I am
        wrong? I have got a new structure here taking your advice on-board, please provide feedback:

        TableName: PatientDetails
        patientRef
        surname
        firstname
        dob
        address1
        address2
        address3
        postcode
        consentpost
        mobile
        consentMobile
        telephone
        consentTelephone
        ethnicity
        sex

        TableName: Test
        testID
        patientRef
        staffID
        screeningSitecode
        testDateRequested
        reasonForTest
        SpecimenType
        testResults
        testStatus
        stisymptoms
        testResultsdateCompleted

        Tablename: Staff
        staffID
        Staffname
        staffdepartment
        staffrole
        staffloginID
        staffpassword
        access_level

        Table – ScreeningLocation
        screeningSitecode
        screeningSiteName

        Table – Drugs
        drugID
        drugName
        Dose

        Table – Partner
        partnerID
        patientRef
        partnername
        partnerphonenumber

        Table: DrugPrescription
        drugID
        testID

        Locolobo: The reason why I have DrugPrescription linked to testID is because if the patient is tested positive then they need to be prescibed a drug. is this right or should I be doing something else?

        I have given the relationship between the tables as follows as I see it:

        Patientdetails, Test ( 1- many)
        patientdetails, Partner ( 1-many)
        ScreeningLocation, Test ( 1-many)
        Staff, Test ( 1-Many)

        Is this right?

        Many thanks

        Steven

        • #2538260

          Test Table

          by locolobo ·

          In reply to Thank you –

          The new Test Table structure looks OK to me. It really depends on the data that goes into it. If the field testID is your primary key for Test you need a field for the Test Name. If testID is the name of the test then I am assuming your primary key is a combo key something like (testID, patientRef, testDateRequested). That is not the only possible way to do that it is just the easiest I saw. For instance, maybe you don’t need a Test Name because the results make it obvious?

          Based on the above I am assuming two more relationships;

          Test, DrugPrescription (1-Many)
          DrugPrescription, Drugs (1-Many)

          Or did I get that backwards?

        • #2538234

          re: test table

          by jai_comp ·

          In reply to Test Table

          Dear Locolobo,

          Thanks again for your reply.

          I am just doing the database for one type of disease at the moment. Hence I haven’t included the name of the test, as it is obvious the database is particular for that disease. I assume if I have to expand the database to accomodate several diseases then I have to create a new table – Disease (diseaseID, name) and link it to the Test table that would be carried out on that disease…

          Thanks you pointed out the two more relationship that I missed out earlier.

          steven

    • #2520390

      some suggestions

      by damian ·

      In reply to Help with normalisation

      Assuming that the [patientRef] field this the primary key for the table [PatientDetails]

      Table: Drug Prescription
      drugID
      testID
      patientRef <---- this field can be removed as the patient is implied by the Test table (referenced by the testID) Again with TableName: TestDetails testID patientRef <--- this could be removed for the same reasons as above. reasonForTest SpecimenType testResults testStatus stisymptoms testResultsdateCompleted You can keep those fields, but this requires both tables to be updated with the patient ref, and in a non-atomic transaction you could be left with either one of the patientRef's not populated, or perhaps worse 2 different patientRef's Hope this helps Damian

Viewing 2 reply threads