11 Replies - 11399 Views - Last Post: 29 August 2011 - 11:51 AM Rate Topic: -----

#1 jalo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 20-March 11

ERD help

Posted 15 August 2011 - 11:21 PM

Create an E-R diagram for a medical clinic, using the following business rules. Use Crow's Foot notation:

• A patient can make many appointments with one or more doctors in the clinic, and doctor can accept appointments with many patients. However, each appointment is made with only one doctor, and each appointment references a single patient.
• Emergency cases do not require an appointment. However, an emergency is entered into the appointment book as “unscheduled” for appointment management purposes.
• If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment.
• Each visit updates the patient’s records to provide a medical history.
• Each patient visit creates a bill. Each patient visit is billed by one doctor, and each doctor can bill many patients.
• Each bill must be paid. However, a bill may be paid in many installments, and a payment may cover more than one bill.
• A patient can pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company.
• If the bill is paid by an insurance company, the deductible is submitted to the patient for payment.

Does this look right or am i completely doing it wrong? Im new to databases

Posted Image

Any help is appreciated

Is This A Good Question/Topic? 0
  • +

Replies To: ERD help

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5643
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: ERD help

Posted 16 August 2011 - 08:02 AM

I'm not sure what the dashed line means...

"A patient can make many appointments" Stop there. You've given a "1 or more" where is should be "0 or more." You did the same on the doctor side. The appointment association looks fine.

"Emergency cases do not require an appointment." This is to fake you out. It still needs an appointment record, which will be designated as "unscheduled."

"If kept, an appointment yields a visit with the doctor specified in the appointment." You have an appointment that can yield many visits, which doesn't look right.

Walk each of these through. You're just translating language into boxes and lines. If it helps, forget the boxes and lines and write it out with words.

e.g.
Patient
	can have one or more Appointments

Doctor
	can have one or more Appointments

Appointment
	must have one and only one Patient
	must have one and only one Doctor
	can have zero or one Visits

Visit
	must have one and only one Appointment




Hope this helps.
Was This Post Helpful? 2
  • +
  • -

#3 jalo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 20-March 11

Re: ERD help

Posted 19 August 2011 - 03:06 AM

Hi thank you so much, this helped me alot!

Here is my revised ERD when applying your tips.

Posted Image

A few questions though:
The appointment to visit association means it is an emergency because the appointment does not get to the doctor, then a visit. Is this right?

I created a new relationship between doctor and bill because it said doctors each doctor can bill many patients. But then it says a visit creates a bill so im still confused on that part -- is there suppose to be a relationship between doctor and bill?

Also, does my bill relationship look right. I removed the composite entry between payment and bill entity, but i'm not too sure why as im new to databases. When it says patients can pay in many installments and they can cover many bills, would this mean i would need a composite because so far it Bill only has a one to zero or many to payment, when it should be zero or more on both sides?

Thank you
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5643
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: ERD help

Posted 19 August 2011 - 06:05 AM

The problem with empty boxes and crow's feet is that they remove you from the actual business of defining a database schema or object hierarchy.

You're drawing this:
Doctor 0:* Appointment
Doctor 1:1 Visit
Doctor *:0 Bill ( this one looks wrong, should be probably be the other way around )
Appointment 1:1 Visit
Visit 1:1 Bill



Consider just:
Doctor 0:* Appointment
Appointment 1:1 Visit
Visit 1:1 Bill



How do I find the Doctor associated with the Visit? Vist->Appointment->Doctor. The Bill associated with the doctor? Bill->Vist->Appointment->Doctor. You don't have to draw all the lines, just the immediate neighbors. This makes a lot more sense when you're not in the clouds making pictures.

Some tables:
Doctor
 - DoctorId (PK)
 - Name
 - ...

Appointment
 - AppointmentId (PK)
 - DoctorId (FK)
 - PatientId (FK)
 - ...

Visit
 - VisitId (PK)
 - AppointmentId (FK)
 - ArivalTime ...

Bill
 - BillId (PK)
 - VisitId (FK)
 - ...



Looking at this, I think I'd include DoctorId in Visit too. Because it strikes me that a Visit mightn't have the same doctor as the one scheduled in the appointment. However, that's more complex than your rules require.

Anyway, I think you need less lines, but it looks like you have the idea.
Was This Post Helpful? 1
  • +
  • -

#5 jalo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 20-March 11

Re: ERD help

Posted 21 August 2011 - 08:27 PM

Hi, thank you for the advice. I decided to start from make a new diagram from scratch. Changes i made is no association from doctor to appointment, created a new entity for the patients record, and deleted the claim entity. I also added PK and FK to the entities and made the appropriate relationships and the 1:1 side has changed to 0:1 for some reason. If i make the relationship "Identifying" it reverts back to 1:1 and the Foreign key changes to a primary key on the entity with the many side. e.g. in the Doctor entity the PatientID becomes a primary key and the line changes from dashed to solid. Im thinking this would make it a composite entity as it has 2 PKs - do you think i need some of those for this database structure

Posted Image

edit: program i am using to create the graph is MS visio 07

This post has been edited by jalo: 21 August 2011 - 08:27 PM

Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5643
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: ERD help

Posted 22 August 2011 - 05:06 AM

Well, if you're actually considering a database schema, then the lines become clearer... and your diagram more confusing.

Patient
- PatientID (PK)
- AppointmentId(FK)



So, through this associate, you a Patient can only have one Appointment. Ever. Visit and Appointment is confused in several ways. It's unclear the association between Patient and Doctor, though the way you have it now, the Doctor can only have one Patient. Not sure what Record does for you at all.

Think in SQL. How will you store this data? How will you retrieve it?

I don't normally diagram. Database schemas are documentation, no doodles needed. If forced to show something, I'll use Dia. I've used Visio, but I'm not fond of it since Microsoft bought it.
Was This Post Helpful? 1
  • +
  • -

#7 jalo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 20-March 11

Re: ERD help

Posted 26 August 2011 - 08:43 AM

Sigh im so stupid, i really have no clue what to do atm :s

Posted Image
Was This Post Helpful? 0
  • +
  • -

#8 tet  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 21
  • Joined: 03-September 07

Re: ERD help

Posted 29 August 2011 - 06:19 AM

Since you're new and you probably don't know queries I'll go right to the point with what you're doing wrong. A patient table cannot reference more than one appointment as a Foreign Key. Because if you have more than one appointment with a patient, how will you be able to put all those appointment primary keys (the FK) in a single row?

When you have a "one to many" relation, you include the PK of your "one" table in your "many" table, but not the other way around. For example it would mean that you include the patient ID in your appointments table so you can identify which patient it belongs to (because an appointment can only have one patient), but not the other way around (as I've explained before).

Data is never really foreign key material. A foreign key is just that, a primary key of a table that is present in another table for reasons you will likely find out on your own. First and Last name is just data and is not any type of unique identifier (i.e. you can have 2 people called peter, or 2 people with the last name smith). Therefore do not tag those are FKs.
Was This Post Helpful? 1
  • +
  • -

#9 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: ERD help

Posted 29 August 2011 - 07:16 AM

Hi,

I believe you are incorrect tet. It makes perfect sense for the patient table to have a one to many relationship with the appointment table. Each appointment record would represent an interaction between one patient and one doctor.

Quote

Each bill must be paid. However, a bill may be paid in many installments, and a payment may cover more than one bill.


This suggests that bill and payment have a many to many relationship and you should have an associative table between the two.

Quote

A patient can pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company.

This says to me that the bill_payment table should have FK's from the patient and insurance tables. However they would be nullable fields so the bill_payment would have either a patient fk(if they paid it themselves) or a insurance fk(if the insurance company paid it). There may be a better way of doing this, but it's my first inclination.

Quote

If the bill is paid by an insurance company, the deductible is submitted to the patient for payment.

You should have a deductible table with FK's from the patient,insurance and bill_payment tables. Each record would represent the deductible from the payment the insurance company made.

I hope this helps, I could very well have made some mistakes here. If so by all means someone correct me :).
Was This Post Helpful? 1
  • +
  • -

#10 tet  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 21
  • Joined: 03-September 07

Re: ERD help

Posted 29 August 2011 - 07:21 AM

View PostJstall, on 29 August 2011 - 07:16 AM, said:

Hi,

I believe you are incorrect tet. It makes perfect sense for the patient table to have a one to many relationship with the appointment table. Each appointment record would represent an interaction between one patient and one doctor.

That's exactly what I said. I was just explaining in detail what baavgai meant when he last replied concerning the Patient table having an appointment id foreign key (which is wrong).
Was This Post Helpful? 1
  • +
  • -

#11 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: ERD help

Posted 29 August 2011 - 09:12 AM

Ahh, forgive me, I misinterpreted what you wrote. My mistake .
Was This Post Helpful? 0
  • +
  • -

#12 tet  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 21
  • Joined: 03-September 07

Re: ERD help

Posted 29 August 2011 - 11:51 AM

it's quite alright, we're only human after all
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1