Question

Locked

Help with normalisation

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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

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?

Collapse -

Thank you -

by jai_comp In reply to Could you explain the tab ...

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

Collapse -

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?

Collapse -

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

Collapse -

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

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums