Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008

    Does this ER diagram make any sense to you?

    Hi, I am a noob at database design

    and the first task that i am required to do is to create an ER diagram before creating a data dictionary

    ER diagram is attached

    Thanks for any replies in advance
    Attached Thumbnails Attached Thumbnails databaseProject5.jpg  
    Last edited by Konfuction; 11-26-08 at 04:49.

  2. #2
    Join Date
    Jun 2005
    No not really, but then without knowing the details about your case study, one cannot conclude the accuracy of the diagram. Luckily, the case you present seems to be a fairly simple one and most of us have been to a clinic before so we have some basic idea of how they operate.

    When starting to design any database model, you should always begin with gathering information about the business. You can begin with documenting the job descriptions and tasks of the people that interact with the system you are modeling. If you will be developing a new system, you also have to gather details about any new requirements that the new system needs to fulfill. From your documentation you will be able to find entities (tables) and their relationships.

    So let's begin with a simple description of the business in your case study:

    A travel vaccination clinic provides patients with vaccines to protect their health when traveling abroad. The current staff of 5 has been having issues with managing patient appointments and because there is no record of who made what appointment, there have been many arguments regarding timetable conflicts. Current methods of accounting and payment processing are no longer satisfactory and need to be updated, preferably to a computerized system. There have been many problems with invoicing patients due to missing information of which vaccines they have received and it is not always known which doctor administered the treatment.

    The business details above could be easily deduced from your conversation with the employees.

    The next step is to analyze the information you have collected to find out that the new system needs to be able to do. We look for nouns and verbs which represent tables and table relationships. So the nouns that stand out are: clinic, patient, staff, appointment, timetable, payment, invoice, vaccine, doctor and accounting. We can reduce the list by removing nouns that represent the same thing like, a doctor is part of the clinic staff, appointment is the same as timetable and accounting is the same as invoicing. What we have left are the nouns that represent the tables, just like in your diagram.

    Tables: Clinic, Staff, Patient, Appointment, Vaccine, Invoice and Payment.

    Now comes the fun part of figuring out their relationships. The amount of relationships really depends on how the various entities are connected. Patients make an appointment at a certain clinic, so does this mean that the Appointment table should reference the Clinic table? This depends if we are dealing with a One-to-One or One-to-Many relationship between the Clinic and Appointment. We know that an appointment can belong to only one clinic, but if the Clinic table holds details about more than one clinic, we need to specify which one. To keeps things simple let us assume that we are dealing with only one clinic.

    From our business description we realize that:

    •A clinic employs staff (doctors, receptionist etc). So we have a One-to-Many relationship between Clinic and Staff.

    •Staff makes appointments for a patients, so we need a relationship between staff and appointment, and patient and appointment.

    •Doctors (Staff) administer the vaccines. This is a Many-to-Many relationship because each doctor can administer many vaccines and each vaccine can be administered by more than one doctor. A Many-to-Many relationship requires us to introduce a new (junction) table that will allow us to break up the relationship into two One-to-Many relationships. Let us call this table Treatment. So, a doctor administers many treatments and each treatment uses one vaccine.

    •Logically, we can link the treatment to the patient, doctor (staff), appointment and invoice, where each patient have one or more treatments, doctors administer one or more treatments, and an appointment and invoice are for a specific treatment. There is one shortcoming that is not easy to see at first. Since we have linked a treatment with a single vaccine, there is no way for a patient to get more than one vaccine for one single treatment. So what, you might think, a patient can have consecutive treatments for different vaccines. That is correct, but this would break the relationship between the Appointment table and cause redundant data to be stored in the Treatment table for linking it to the staff, patient and invoice. We also, cannot have a Many-to-Many relationship between treatment and vaccine. The solution is to introduce a new table called Visit. A visit will allow us to link more than one treatment to a patient. If you really think about it, this makes logical sense, since when you go (visit) to the doctor you can have many treatments performed by more than one doctor. So the new Visit table has a One-to-One relationship to the Appointment table, a One-to-Many relation to Treatment, a Many-to-One relation to Invoice (an invoice can be for more than once visit) and a Many-to-One relation to Patient (a patient has one or more visits).

    •Finally, the Payment table is related to Patient (Many-to-One) and to Invoice (One-to-Many).

    I think this is enough for now. You can tell I was bored. LOL
    Make a new diagram and post any questions you may have.

    Good luck.

  3. #3
    Join Date
    Nov 2008
    Thank you very much for your thorough reply, I will create the amended diagram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts