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.

enter image description here

Does that look right?

  • 1
    So each patient will only ever be involved in one trial? And each practitioner will only ever see a given patient once? Do practitioners and patients visit multiple centres? Feb 13, 2014 at 21:10
  • Unless a patient can be in more than one trial, at more than one center and each practitioner can only be involved with one trial (both cases are for the life of this database) it looks good. Otherwise it needs a little tweaking. EDIT: beaten by 20 seconds. Feb 13, 2014 at 21:10
  • 4
    Practioners can be patients too...Also a table name like "DATA" suggests you haven't fully thought out what it does. Feb 14, 2014 at 20:47
  • What software did you use to draw the ERD?
    – Nate
    Oct 5, 2014 at 18:55

2 Answers 2


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.