If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Help with ER Diagram

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 5
Help with ER Diagram

Hi, i need to create a ER diagram for a clinic. Below is my case study:


Travel Jabs Clinic Database

A company called Travel Jabs provides private travel immunisation throughout the UK. This service is provided through various clinics located in the main cities of the UK. The Director of Travel Jabs is concerned that there is a lack of communication within the company and particularly in the sharing of information and resources across the various clinics. To resolve this problem the Director has requested the creation of a centralised database system to assist in the more effective and efficient running of the practice. The Director has provided the following description of the current system.

System Overview:

Travel Clinics
Travel Jabs has many travel clinics located in the main cities of the UK. The details of each clinic include the clinic number, clinic address (consisting of the street, city, county, and postcode), and the telephone and fax numbers. Each clinic has a Manager and a number of staff (for example, nurses, secretaries, cleaners).

Staff
The details stored on each member of staff include the staff number, name (first and last), address (street, city, county, and postcode), home telephone number, date of birth, gender, job description, national insurance number and current annual salary.

Patients
When a patient first contacts a clinic of Travel Jabs, the details of the patient are recorded, which include a patient number, (first name and last name), address (street, city, county, and postcode), date of birth, gender and home telephone number.

Vaccines
Travel Jabs provides various vaccines at a standard rate across all clinics. The details of each vaccine include a vaccine number, a full description of the treatment and the cost.

Invoices
Each patient is invoiced for the vaccine(s) and the details recorded on the invoice include the invoice number, invoice date, patient number, patient name and full address. The invoice provides the cost for each vaccine and the total cost. Additional data is also recorded on the payment of the invoice, including the date the invoice was paid and the method of payment (for example, cheque, cash, credit card). Invoices are issued on the same day as the vaccines are administered.
The invoice does not state the member of staff who administers the vaccine(s) but this information should be stored in the database.

Appointments
Every patient must make an appointment. The details of an appointment are recorded and include an appointment number, patient number, patient name (first name and last name), home telephone number and the appointment date and time.


I have attached the ER diagram i have created. Can someone please let me know if I have done it correct or if i need to change anything.
Attached Thumbnails
Help with ER Diagram-erdiagram.jpg  
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 63
It looks pretty decent.

You excluded most of the foreign key attributes even though you showed the relationships. You have patientNo in Invoice but don't show the relationship. You have some denormalized info like patientName and patientAddress in Invoice, but this is fine to record historical information. However, I see no reason to duplicate the patient's info in Appointment. Your many-to-many relationship will end up being converted into a juntion table between a pair of one-to-many relationships, but I'm fine with many-to-many in an ERD.

Based on your case study, there's more to be done. You haven't implemented the clinic manager relationship. You should probably record the cost of each vaccine on the invoice for the same reason you record the patient's name and address on it. Also, when these assignments list types (like job descriptions or payment methods), they usually want you to normalize that and make separate tables defining those types.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 5
Quote:
Originally Posted by reaanb View Post
It looks pretty decent.

You excluded most of the foreign key attributes even though you showed the relationships. You have patientNo in Invoice but don't show the relationship. You have some denormalized info like patientName and patientAddress in Invoice, but this is fine to record historical information. However, I see no reason to duplicate the patient's info in Appointment. Your many-to-many relationship will end up being converted into a juntion table between a pair of one-to-many relationships, but I'm fine with many-to-many in an ERD.

Based on your case study, there's more to be done. You haven't implemented the clinic manager relationship. You should probably record the cost of each vaccine on the invoice for the same reason you record the patient's name and address on it. Also, when these assignments list types (like job descriptions or payment methods), they usually want you to normalize that and make separate tables defining those types.
do i have to state what attributes are foreign keys when using UML notation
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 764
Quote:
Originally Posted by NFS View Post
do i have to state what attributes are foreign keys when using UML notation
I believe what reaanb was pointing out is that you need an fk from parent to child table which creates the relationship.

For example there is no fk in vaccine which indicates the prescription. Can there be more than one vaccine per prescription? Or none?

Quote:
Travel Clinics
Travel Jabs has many travel clinics located in the main cities of the UK. The details of each clinic include the clinic number, clinic address (consisting of the street, city, county, and postcode), and the telephone and fax numbers. Each clinic has a Manager and a number of staff (for example, nurses, secretaries, cleaners).

Staff
The details stored on each member of staff include the staff number, name (first and last), address (street, city, county, and postcode), home telephone number, date of birth, gender, job description, national insurance number and current annual salary.
Staff are not (physically) associated with clinic so an fk on clinic table is needed if staff can be associated with one-many clinics. How would you join these two tables without an fk? If you go by post code that may not be a good candidate because post code for staff may be entered incorrectly. Second, the staff table is personal information. Once you enforce the fk then staff associated with a specific clinic is easy enough to identify.

Another thing to consider is a patient can possible go to more than one clinic. How do you associate this relationship?

How are staff and appointment related? How would you retrieve this information?

Good luck.

Last edited by corncrowe; 03-31-12 at 10:16.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 764
I should point out that you can add a relationship either by physical (constraint) or logical. Consider whether or not a business rule is required and enforced. For example: every appointment much be assigned to a staff. If true then the fk constraint would enforce this business rule. If false then adding the staffno to appointment table without the constraint means that is not enforced and an appointment may not have any staff associated.

-- just some thoughts

Here is a good link for database design linky
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 5
I have created a new ER diagram...hopefully this ones is more accurate than the previous one. Any changes i should make or anything i should add??
Attached Thumbnails
Help with ER Diagram-erdiagram.jpg  
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 764
Quote:
Originally Posted by NFS View Post
I have created a new ER diagram...hopefully this ones is more accurate than the previous one. Any changes i should make or anything i should add??
I think you are much closer. What should this flow look like:

Is it clinic has --> patients --> who make appointments --> and are seen by staff --> who prescribe vaccine --> which is billed to patient

I don't believe staff make appointments. And another thing is how do you relate which date a patient visited? I see an appointment table and patient, but how can you tie back the patient to a particular visit?

Looks much better.

Last edited by corncrowe; 03-31-12 at 12:48.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 5
Quote:
Originally Posted by corncrowe View Post
I think you are much closer. What should this flow look like:

Is it clinic has --> patients --> who make appointments --> and are seen by staff --> who prescribe vaccine --> which is billed to patient

I don't believe staff make appointments. And another thing is how do you relate which date a patient visited? I see an appointment table and patient, but how can you tie back the patient to a particular visit?

Looks much better.
I believe the flow is:

clinic has --> staff & manager --> who store details of patient and make appointment when patients contacts clinic --> patient is then prescribed vaccine ---> invoiced to patient.

Can you tell me what relationships are missing and what foreign keys need to be added to what table.

Regarding which date a patient visited, the appointment table has the patientNo as a foreign key so you know what appointment corresponds to which patient. Right?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 764
Quote:
Originally Posted by NFS View Post
I believe the flow is:

clinic has --> staff & manager --> who store details of patient and make appointment when patients contacts clinic --> patient is then prescribed vaccine ---> invoiced to patient.

I tend to agree. I believe the clinic-->staff-->appointment-->patient relationship are much better.


Can you tell me what relationships are missing and what foreign keys need to be added to what table.

patient-->prescription looks ok because you should be able to retrieve when a prescription was given by date and go up the relationships to arrive at appointment date <-- staff <-- clinic

Good job!


Regarding which date a patient visited, the appointment table has the patientNo as a foreign key so you know what appointment corresponds to which patient. Right?

That is fine since you can retrieve a patient history <-- appointment by date <-- staff who administered vaccine at which clinic <-- clinic
I would look at the invoices again and ask yourself how to provide the details and totals? Do you need details and a summary? Do you require more normalized tables? How would you resolve the cost and totals in the same table?

I would also look at how scalable is this model? Would it break if a staff member moved to a new clinic?

Last edited by corncrowe; 03-31-12 at 16:07.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 764
Assumptions 2: A patient can go to any clinic and be seen by any doctor. The doctor can likewise can be associated with any clinic and see patients from any clinic. The patient and doctor may have previously seen each other at any of the clinics. This is the patient entity. The prescription and invoice entities should reflect this relationship.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 63
It looks better with the foreign keys displayed. However, the rest of my comments still stand. Your added assumptions box don't add to the diagram - though you claim that many-to-many will be broken up, you misrepresent your many-to-many relationship. Prescription is not the junction table between vaccines and doctors (prescription doesn't reference a doctor). The many-to-many relationship is still between vaccines and prescriptions. I don't think it's a problem having a many-to-many, but some educators will require you to show it as it will be implemented.

If I seem critical, I apologize. Really, this is a good attempt and you've done much right; it's only because the diagram is close to a perfect answer that I'm able to easily pick out the individual inconsistencies.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 5
I have edited the ER diagram again so hopefully it "satisfies the above case study" better.

I have tried to normalise the diagram so I have removed the patients name and address attributes in the invoice table. I have also removed the attributes patients name, patient number, and patient telephone number from the appointment table. Hopefully the ER diagram is now normalized, so can someone please check this for me.

I have also created a seperate table called Payment for the attributes that regard to the invoice being paid. However I do not know exactly how to establish the relationship between Payment and Invoice table???

Also is the Patient and Invoice table supposed to be linked?? Is it correct that I have removed the attributes from invoice and appointment to normalize the diagram?? Are my relationships between tables correct?? Is this ER diagram able to "satisfy the case study"??

Any help to correct any mistakes in the diagram would be greatly appreciated.
Attached Thumbnails
Help with ER Diagram-erdiagram.jpg  

Last edited by NFS; 04-03-12 at 22:22.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On