I have a complex application under re-development, and am stuck on the
data model which is almost certainly wrong. The application is designed
to handle clinical requests coming into a hospital records system. A
request concerns a single patient, and can come from a hospital or a GP
(general practitioner). Details as follows:
1) The starting point is the requests table. Each request belongs to one
patient.
2) Each patient can have one or many requests.
3) Each patient can have none, one or more than one patient/case number.
Patient/case numbers are not unique to patients - eg 2 patients in two
separate hospitals could have the same patient number.
4) The request can come from a hospital or a GP.
5) GP's belong to GP practices.
6) Each GP belongs to one GP practice, and each GP practice can have one
or many GP's.
7) Clinicians belong to hospitals. Each clinician can belong to one or
more hospitals.
8) Each clinician can have one or more specialities (though only 1
speciality per hospital).
From this requirement I have constructed the following 'primary' data
tables:
requests: id, date, request_number, patient_id, specimen, timestamp
patients: id, last_name, first_name, dob, nhs_number, timestamp
patient_numbers: id, patient_id, case_number
hospitals: id, location_name, organisation_code
clinicians: id, national_code, surname, initials
specialities: id, speciality
gp_practices: id, national_code, address, post_code
general_practitioners: id, national_code, surname, practice_id
And various link tables:
clinician_organisations: clinician_id, organisation, speciality_id
hospital_requests: request_id, clinician_id, hospital_id, timestamp
gp_requests: request_id, gp_id, practice_id, timestamp
request_patient_numbers: request_id, patient_number_id
The primary to foreign key relationships are as follows:
requests.patient_id => patients.id
hospital_requests.request_id => requests.id
hospital_requests.hospital_id => hospitals.id
hospital_requests.clinician_id => clinicians.id
gp_requests.request_id => requests.id
gp_requests.gp_id => general_practitioners.id
gp_requests.practice_id => gp_practices.id
clinician_organisations.clinician_id => clinicians.id
clinician_organisations.speciality_id => specialities.id
patient_numbers.patient_id => patients.id
request_patient_numbers.request_id => requests.id
request_patient_numbers.patient_number_id => patient_numbers.id
The sql to retrieve a request is as follows:
SELECT
patients.id,
requests.request_number,
requests.date,
patients.last_name,
patients.first_name,
patients.dob,
patients.nhs_number,
patient_numbers.case_number,
requests.specimen,
hospitals.location_name,
clinicians.surname,
specialities.speciality
FROM requests
JOIN patients ON (
patients.id = requests.patient_id
)
LEFT JOIN hospital_requests ON (
hospital_requests.request_id = requests.id
)
LEFT JOIN hospitals ON (
hospitals.id = hospital_requests.hospital_id
)
LEFT JOIN patient_numbers ON (
patient_numbers.patient_id = patients.id
)
LEFT JOIN request_patient_numbers ON (
request_patient_numbers.patient_number_id = patient_numbers.id
AND
request_patient_numbers.request_id = requests.id
)
LEFT JOIN clinicians ON (
hospital_requests.clinician_id = clinicians.id
)
LEFT JOIN clinician_organisations ON (
clinician_organisations.clinician_id = clinicians.id
AND
left(clinician_organisations.organisation, 3) =
left(hospitals.organisation_code, 3)
)
LEFT JOIN specialities ON (
specialities.id = clinician_organisations.speciality_id
)
WHERE requests.id = <unique requests.id>
This is for a single request from a hospital - I haven't got as far as
including GP's in the query yet.
The problem is the case number (synonym for patient number) - if a
patient has 2 or more cases numbers registered to them we get multiple
records for the same request (one for each additional case number).
Otherwise, if a patient has 0 or 1 case numbers registered to them then
we correctly get a single result returned.
This suggests the data model is incorrect, at least where patients and
case numbers are concerned. I suspect the problem involves the modelled
relationships between patient_number, patient and request, but I would
be most grateful for any assistance in optimising the data model so that
it returns the correct information.
--
Richard Jones
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
