Hello,
I recently posted a topic with regards to a help with normalisation. I am a medic and trying to develop a database for our clinic. The aim of the development is to create a database to accept several specimen types which could be used to test several diseases (although I initially started the development to accept only one specimen type and perform only one test on it). After several frustration attemps I have comeup with the following structure, which I would like your opinion please.
Table Name: Patientdetails:
patientRef
surname
firstname
dob
address1
address2
postcode
consentpost
mobile
consentMobile
telephone
consentTelephone
ethnicity
sex
address3
pid
Table Name: specimenTaken
episodeID
patientRef
specimenID (label to be attached on specimen)
specimenTypeID
dateofSpecimenTaken
screeningSitecode
ackDate (for lab to acknowledge that they received the specimen)
staffID
specimenStatus
Table Name: diseaserequest
episodeID
specimenID
diseaseID
stisymptoms
result
testResultsdatereceived
additionalNotes
Table Name: Disease (lookup table)
diseaseID
diseaseName
Table Name: specimen_type (looktable)
specimen_type_id
specimenName
In the above tables I am relating patientdetails to the specimenTaken table, which describes the specimen the patient has provided and the number of specimen could range from 1…5 number which is looked up from specimen_type table. The episode ID is the primary key for specimenTaken taken because it describes the group of specimens and different test that could be carried on it at any one time. For the diseaserequest table the primary key is the the combination of episodeID and specimenID which will describe the disease (dieseaserequest) the specimen could be tested on. Please let me know if I got following relationship right and normalisation in the above is ok.
paitnedetails, specimenTaken -> 1 – Many.
specimenTaken, dieseaserequest – >1 – Many (primary key is the combination of episodeID and specimenID as any one specimen could be tested for several diseases)
diseaserequest, disease -> many – 1
specimen_type, specimenTaken -> many -1
Many thanks for your help
Steven