• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1760
  • Last Modified:

ERD diagram - needs checking

Hi. I want to design a simple ERD diagram.

I want a patient to be able to have an appointment. A doctor to 'hold' an appointment. An appointment to prescribe one or more prescriptions. A prescription to consist ot one or more medications. An appointment updates a patients record.

Attached is my take on this. Please could you review this and let me know if it seems integral. Thanks

Also I would like to include a many to many relationship somewhere in here  diagram
  • 2
1 Solution
Joe WoodhousePrincipal ConsultantCommented:
... Homework?

Your model as it stands is simplistic. Every appointment results in at least one mandatory prescription? (Actually in the USA this might not be inaccurate... :) ) Any one prescription is likely only for one medication, BTW. If I get multiple medications prescribed as a result of an appointment each one is on its own prescription. And since a prescription will not be for anything that isn't a medication, I think you can probably collapse those into one table.

You might want to think about how to model a family attending as a single appointment. Similarly prescriptions would be attached more to a patient than to an appointment, but you would want a record of which appointment triggered a prescription.

Also how will you handle an invoice that is only part-paid? What if a patient pays for multiple appointments in a single payment?
graziaziAuthor Commented:
Hi. Thanks for the reply. You raised some interesting questions. I came up with the narrative myself, I'm rehearsing for exams :)

Things I'll need more practise
Joe WoodhousePrincipal ConsultantCommented:
As a general comment I find it more useful to think it through in words and think about what's actually being modelled. At every relationship in your diagram I asked myself "is there a time where this might not be mandatory even though it is currently marked as mandatory?" (Someone doesn't pay their their invoice in full and another invoice needs to be issued for the outstanding amount.) "This is currently marked one to one - is there ever a time when there'd be more than one? (A couple or family jointly attending an appointment with each of them being treated.)

Once you go through it in words like that, the diagram itself is easy. Do the diagram after the analysis, not as *part* of the analysis. :)

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now