ER Diagram for Missed Visits

Question

I have to design an ERD for a hypothetical scenario. In the scenario, the nurse at a rural clinic needs to get summary data regarding patient visits so she can reschedule canceled or no-show visits.

For entities so far I have: patient, provider, and visit

I'm stuck on how to model the relationship between patient and visit without either creating a lot of redundancy or entering a lot of null relationships. I was initially thinking of breaking the visit entity up into no-show, canceled, and completed with 1:1 relationships between them and the patient entity. Would this create a bunch of null entries for the entities that do not apply to the patient, or is it possible to create an inherited relationship where there is only data entered if the relationship applies (much like a dependent and employee relationship)?

Thank you for any feedback/insight you can provide as I work through this.

Answer 1

How about the following

Provider

• Provider ID
• Provider Name
• etc...

Patient

• Patient ID
• Patient Name
• etc...

Visit

• Visit ID
• Patient ID
• Date/Time
• Provider ID?? (Unsure of how this works in the context)
• Completion (Values: "No Show", "Cancelled", "Completed")
• ...further relevant info (such as practitioner id or something. Maybe location, if that isn't contained in the provider)

There's only one visit entity, with values depicting if the patient arrived to the appointment.

Relationships

• Patient/Visit: One-to-Many. Each patient can have multiple visits, but each visit describes only one patient
• Provider/Visit: One-to-Many?? Again, I don't know what provider means in this context. Is it the clinic name? Or the insurance provider? In either case, it makes sense to have a one-to-many relationship with the visit.