I’m pretty new to databases and I’m trying to create some schemas.
I’ve been working away at this one for a while, but I can’t help feeling that it doesn’t look right.
Essentially I have the following situation:
- A given medical trial (of which there can be many) takes place in a varying number of medical centres.
- Each centre has patients and practitioners.
- Practitioners and patients have a consultation, and data is created.
It seems simple enough, but I’ve a feeling I’ve made a mess of it. The resultant database will be MySQL if that’s relevant.
Does that look right?
As a very basic structure it is close.
The one glaring issue is that the relationship between trial and centre is the wrong way around. You should have a CentreId on the Trial table, as I believe there can by multiple trials from 1 centre and not vice versa.
Also you may want to consider adding some extra details to Centre like name etc.
Lastly, the Patient -> Trial relationship may well be a many-to-many relationship and thus you would need to add a table to encapsulate this relationship.
I’m making assumptions based on your question and my experience. Several related “Centers” may be running the same trial (at the same time?, using the same protocol?), and each “Center” may be running multiple trials concurrently, but certainly over time. Data from multiple centers is collected & analyzed at a CentralCenter. The “Practitioner” is active at only 1 “Center” (does not float), the “Patient” is static at 1 “Center”, “Patient” can only be in 1 “Trial” at a time, and the “Data” table represents 1 “Patient” visit to the “Practitioner” for given “Trial”.
There needs to be a MasterPatientTable containing normal address, phone, DOB fields (eliminates “age”, ‘gender” from “Patient” table. Also I’d rename “Patient” to “PatientTrial” or similar) but add TrialStartDate to “PatientTrial” (all patients do not necessarily begin the trial at the same time); TrialEndDate can be a field in “PatientTrial” or inferred from the TrialProtocolTable (which I don’t see in your definition). The “PatientTrial” table then truly represents this patient in this trial.
“Data” needs “TrialID” or in the future, you may never know which “Trial” this visit related to.
A given “Trial” usually runs for a period of time, but all centers may or may not be running it for the same period of time. Ditto the “Trial” protocol may be slightly different among “Centers”. Are you defining the trial protocol in this database, ie how long does it last, what are the milestones, methods, proofs, etc? Your “Center” table probably should become CenterTrial and needs Start and End date fields, and a MasterCenterTable as described for “PatientMaster”. A given “Trial” usually has PatientResponseSheets, completed by patient as TrialInstance occurs and later entered into system. For instance patient has migraine symptoms, takes TrialPill, records results into provided form; or patient feels destructive anger, takes anger management steps, records results into provided form. Without at least MasterProtocols defined, creating PatientResponseSheets and recording patient provided data is not possible.
There’s a lot to consider in a system of this type. I make these suggestions from experience, having designed & written a system for mental health clinic studies (trials), as well as having been a patient in multiple migraine treatment studies.