Question

Locked

help with database relationship

By jai_comp ·
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

This conversation is currently closed to new comments.

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

All Answers

Collapse -

Maybe someone with more experience will comment,

by LocoLobo In reply to help with database relati ...

but I think it's normalised enough. My only suggestion at this point is to populate the tables with representative data and see how it works. You can do this on paper if you want. You want to test the relations and make sure they make sense to you.

If it's not too much of a problem create the tables in your database and populate them with some real (or fictional) data. Test the queries. If you can now is the time to iron out some of the kinks.

Good Luck!

Collapse -

Primary Key

by bsmith523 In reply to help with database relati ...

For specimenTaken, I would make specimenID as the primary key. I assume that multiple specimens could be taken for a specific episode. I am assuming an episode is a specific medical event. I would then make another table i.e. episodeDetail which would have episodeID as the primary key. patientRef and other fields to describe the episode. Remove patientRef from specimenTaken since this will be related through the episodeID. Then for diseaserequest get rid of episodeID and add a requestID as the primary key. so your relationships should look like this:

patientref.patientDetail (1) -> patientref.episodeDetail (many)
episodeID.episodeDetail (1) -> episodeID.specimenTaken (many)
specimenID.specimenTaken (1) -> specimenID.diseasrequest (many)
specimenTypeID.specimenTaken (1) -> Specimen_Type_ID.specimen_Type (many)
DiseaseID.diseaseRequest (1) ->
DiseaseID.Disease (many)

Good luck.

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

Related Discussions

Related Forums