Database Design

We can help you understand what an entity diagram is, but we won't do it for you. First you'd need to decide what major data "entities" you have ... for example, you'd likely need to store information about Patients, Clinics, Doctors, etc etc ... those would be "entities", and each would likely require a table. You'd also need to determine the "properties" of those entities (eg, the Name of the clinic, Location etc etc) and decide which of those might be best represented by related tables, or by directly storing this info in the main tables ... from there you simply keep working the data, refining it until you have the basic data entities, properties, etc in some sort of logical format. From there, you'd then sketch your ER diagram.

That is right that is a homework.. but if I upload the ER I attempted to draw, could someone to tell me if I am on the right track, please...  I don't want anyone to do my homework, just need some ideas to compare with mine. Thanks a lot

How is a Vaccine related to an Appointment?

How is a Vacinne related to Staff?

How is an Invoice related to a Vacinne?

Can a patient have more than one Vacinne on a single Appointment?

Would an Invoice ever have more than one item?

Vaccine to appointment... I need to record what vaccines are administered at an appointment (yes, a patient can receive more than one vaccing per appointment)
Vaccine to staff .. staff that administer a vaccine also need to be recorded in the db
Invoice to vaccine .. Invoice records all vaccine adminestered per appointment (thus invoice can record more than one vaccine..

Thank you for your help.. much appreciated.. as I said, really need to know if I am going in the right direction and if it makes sense.

Good start.

I wrote this while LSMConsulting was writing his. So there is some overlap.

I assume each arrow represents a 1-many relationship with the many at the arrowhead.

I am not an expert at the formalism of ER diagrams so this is my opinion: I recommend putting the 1 side of the arrow next to the id that relates to the other table. For example the left side of the arrow from patients to appointments should start at patient_id. Especially in the case of the vaccines-clinic connection.

Check for missing FKs.

To check the correctness of the direction of an arrow - translate to a sentence e.g. one patient has zero-or-more appointments. Some of the arrows point the wrong way. At least one arrow should not be there. Some of the relationships are 1-1 rather than 1-many.

Did you design the tables? If so consider that staff and patients have a lot in common. What does that suggest?

These are questions that YOU need to answer ... I'm not concerned with them, but merely posted them to perhaps encourage you to examine why you're tracking these as you are.

<yes, a patient can receive more than one vaccing per appointment>

How would you record this?

More ???:

How are StaffId and ApptID related to Vaccines?

Can a Patient have more than one Appointment, at more than one Clinic?

Can a Patient make a partial payment?

These are things which you need to think about before you can really build your ER diagram. At this stage, you've got a fairly decent grasp of what's going on, but you need to rethink some of your relationships.

Thank guys for the tips, will have a look at the arrows and relations...  Now that I am trying to populate the tables, just to see the connections and how the tables link.. it looks like I have no data to put in the  vaccine table in the fields staff id, and appointment id.. but I do need to record who administered the vaccine and which vaccines were administered at each appointment.  Should I create some entity in between vaccine table and the appoinment table and that entity would also then be connected to the invoice ... it's all so confusing.. Thanks for your help

I'm curious - I'd think the course-instructor-textbook-whatever would provide you with the tools and methods for solving this problem. What is missing that it is so confusing?

The more effort we EEs put into this problem the more it looks like we are becoming the course-instructor-textbook-whatever. That is not our role - to make up for school deficiencies. Or else you have not utilized the resources available or don't have the time for that.

Which is the case?

You are probably absolutely right .. and I am not going to make excuses but one, it is quite tough to try to work full-time and study full-time, so I did neglect this subject. Nevertheless, I am working on it quite hard now to catch up and got some good ideas here now as well. I sincerely apologize for wasting your time and do realize that this is not the purpose of EE. Being honest, i looked for a bit of a shortcut. But back to books and drawings now. Thanks again and sorry.

absolutely right - not probably (I don't make sense here now at all)

One additional thought that could help provide a sanity check after you create the database -- Visio can "reverse engineer" a database, i.e., you can create a database model diagram from an existing database. I'm not suggesting it will be a perfect diagram but it would give you something to compare with the diagram you created from scratch.

You didn't mention which version of Visio you're using. The instructions below apply to Visio 2007 -- if you're using an earlier version the name and location of the template might be different but the reverse engineering wizard is still there.

In Visio 2007:

• select File/New  
• select the Software and Database folder  
• select the Database Model Diagram  
• after the new document opens, select Database/Reverse Engineer... and step through the wizard  

Hi czechmate,

That looks like a pretty good start, :), but check the relationship portion of the diagram.  The one-directional arrow indicates that a table has a dependency on a data item in a different table.  The arrows in your diagram don't seem to represent what I would expect.  Directly or indirectly, the tblappointments table is dependent on data in every other table.  I would expect that that would be the characteristic of tthe tblinvoices table as you can't really have an invoice without a clinic, patient, appointment, and perhaps staff.


Good Luck,
Kent

Thanks Kdo, that really makes sense now that I am reading it.. will try it out if it gives the required results.

Does this make sense... I tried to place the invoice in the centre..and play around with the other associations a bit

Based on the table structures, it does.

But it seems to me that the general relationship between the appointments and invoices tables is backwards.  If I were responsible for the design, the arrow between the tables would be reversed and the invocies table would have a field to indicate that it is tied to an appointment.


Kent

It does make better sense.. I was thinking about it too but sometimes you need to hear it from someone else to belivieve your judgement is right. Thanks for the input.. the diagram really starts to make much better sense now!

czech,

That's looking good. i think that if you want an A+, you could add a table called Position or StaffType or some such where the staff table would foreign key into it (instead of having a tblStaff.Position column). That would allow you add additional staff types without much tweaking.

Gender is another possibility... That's right. i've worked on a number of US-based clinical applications and have seen as many as seven gender types!

My only quibble would be the relationship between Invoice and Clinic ... you're already representing this through the Appointment, thus I see no reason to store ClinicID in tblInvoice, and it's seem to me that a Clinic is more closely related to an Appointment than it is an Invoice. IOW, you can always "get to" the ClinicID through the Appointment (to which the Invoice is correctly related), thus there's no need to store ClinicID in tblInvoice.

Thanks guys for more tips, guess I will have to do more re-thinking once I win my fight against a nasty flu bug (which has also been the reason for not being online for the past 3 days) ...  Thanks guys a lot

Regarding the latest post of LSMConsulting... I understand what you mean by that but wouldnt the same principle apply to the relation tblinvoice and tblpatients, as there, too, it can be accessed through the table appointments, no?

My point is there doesn't seem to be any reason to relate both an Invoice and an Appointment to a Clinic. Choose one but not both. Further, there doesn't seem to be any reason to relate a VaccineSet to a Clinic ... to me, a VaccineSet would be part of an Appointment or Invoice, assuming that a VaccineSet is a group of Vaccines that is administered to a Patient.

Which brings up the VaccineSet table. Is this a many-to-many Join, where you'd store multiple Vaccines given to a specific Patient (i.e. during an Appointment)? If so, the you're not storing that correctly ... IF this is the case, then you'd store (a) tblVaccines.VaccineID and (b) tblINvoices.InvoiceID in your tblVaccineSets. In this particular instance, tblVaccines would be a "lookup" table to tblVaccineSets. Also, you would NOT store tblClinic.ClinicID ... a VaccineSet has nothing to do with a Clinic (from a data standpoint, at least) but would instead be more closely associated to an Invoice or Appointment (IMO, that is).

Note these are simply my views ... there are many different ways to store data, but IMO you've got a few little issues with this. Overall, however, a very good start and a good learning experience for you!

OK, I have been playing with it a bit more and have come to this, I am sure there is a bummer in it somewhere again but now I am not sure of anything anymore :-) . Please could you once more have a look at the diagram and tell me what you think

Hi czechmate,

I'm still not comfortable with the structure around the invoices table.  It would seem to me that an invoice can be generated by an appointment or the administration (delivery) of a vaccine.  Depending on the business rules, an invoice could be the result of multiple appointments, vaccines, and/or vaccine sets.

If a patient is prescribed a vaccine set, it may be that the vaccinations occur over an extended period of time.  If so, it's appropriate to bill as each vaccine is administered.  Or is the vaccine_set simply the link item between the other tables with a vaccine set referencing a single vaccine?

Still, it seems that the invoices table should be a parent/child with the appointments table.  An appointment generates a bill.  You can see the doctor without getting a vaccine.  But you cannot get a vaccine without being in the doctor's office.


Kent

The vaccines are supposed to be invoiced per appointment according to the task list.. The vaccine set table should be a link in a many-to-many relationship between the tblvaccine and tblappointments as a vaccine can be administered at many appointments and one appointment can serve to administer many vaccines --- not sure if I am totally right.

Anyway, I dont know any more where to put the invoice and how to relate it to what.. I though it should be coming off the appointment but then the vaccines need to be related to it as well... LOST LOST LOST

I don't think that you're lost at all.  In fact, you seem to have a pretty good handle on what you need.  :)

Let's go back to the invoice relationship.  Nothing happens between the doctor and patient without an appointment.  Even walk-in care can be logged with the appointment structure.  So let's tie the invoice to the appointment.  (When you go to the doctor, you have to pay.)

Additional charges may be incurred due to the delivery of medication -- the vaccine_set table.  And because it's possible to receive more than 1 dose per appointment (either multiple doses of the same vaccine or multiple vaccines) the relationship between the appointment and vaccine_set tables must be reversed.  The vaccine_set will identify the appointment during which the dosage was administered.

Now the only big decision would seem to be if a single invoice will cover multiple appointments, multiple invoices can be produced from a single appointment, or if the relationship is 1-to-1.  I would expect 1-to1 or 1 invoice to multiple appointments (e.g. monthly billing).


Kent

This made it a bit clearer. Thanks for that. The invoice - appointment should be a 1-to-1 relationship.. but at single appointments more than one vaccine can be administered.

Should the invoice come off the appointment then or  the vaccine set like this

I'd still change this so that the link between the invoices and appointments tables was direct and that access to the invoice table from any other table was via the appointment table.

The appointments table controls/records events.  Who was scheduled with an appointment, which clinic, what date/time, what services were rendered, etc.  The invoice is just a summary of the price of the events that transpired due to an appointment.


Kent

So it should look like something like this

I like it.  :)

Did I mention that staff and patients are subsets of persons? Since there are so many common fields I'd make a person table to hold the common stuff; this would then relate to patient and staff tables which hold the unique data. This also allows a staff member to be a patient without duplication of data which is a no-no.

I enjoy this discussion as it shows that there can be different ways to model an application.

This is the first time I have heard of "task list". What is that / where did it come from? Could we see it?

I distinguish between appointment and visit. Appointments schedule visits. They are a "plan". The actual visit is a different thing.

Looking down the road I see that during a visit a patient receives services (as we know from the inevitable form clinics use to report what was done for the patient). Vaccination is one of many services.

Now as I look at patient, staff and clinic I see a new common entity "address". Should this be a separate table?

And don't many of us have more than one phone number?

And on and on ...

Hi ramrom,

I don't see enough difference between an appointment and visit to separate them.  At least not for an exercise.  (In the real world, I'm one of the "great normalizers" and probably would separate them.)  An appointment is simply the scheduling of a visit.  By using NULL for the appointment time/date the same structure can differentiate and manage scheduled appointments/vists and walkins.  If the number of no-shows is relatively small, the waste generated by the unused fields in the appointment record is more than offset by the simpler design.

And I agree about normalizing the people tables.  In the "real world" I would definitely normalize the people demographics so that there would be staff, patient, and demographics tables.


Kent

Thanks Ramrom for your input, I was also looking for similarities between the staff table and patients table.. and I am sure someone like you knows the correct way to apply it but I am a student and I am sure that it would kind of confuse my tutors... who I believe dont know as much as you guys.. As to the task list - that is the DST.doc I have uploaded at the beginning that contains the requirements of the database.. I have been killing myself with for days.

Anyway, I have added a tblvaccine_stock between tblvaccines and tblclinics as is seems to me a many-to-many relationship (Some of you didn't like to connection there at all but I somehow think how otherwise to control the stock - stop me if I talk crap please - or if I am browsing into even more complicated design)