Normalization Issues - Please help

Hi guys,
I have to create a database, and just finding doing the erd a bit tricky.
I have to create a medical appointment database, yet do not know the best way of
approaching it. The db will be created in access and Asp.
Basically, it involves 2 main entities.
- Patient
- Doctor.

To put it in a nutshell,
Doctors have a roster, they should be able to select 7 day working week, to indicate when they are available and at what time.  So for Doctor Jim, he can select three weeks in advance and which day and time he is available. So he can say select Mondays, Wednesdays and Thursdays as his/her available days. Then they can select the timeslot of when they are available. Usually the timeslots are divided by 30min, 15min, it really depends on the doctor.

Patient:
When the patient comes to do a booking online, he/she must be able to book an appointment with a specific doctor, and the time that doctor has allocated for appointments. Once that booking has been done, there can be no other bookings in that timeslot, as we want to prevent cross-bookings.  So basically, the patient can only see appointment time periods that are available. They can choose up to 3 time periods.  The patient enters all details such name, phone, DOB, and email. A confirmation email is sent to both patient and selected Doctor.  

------
To expand on the doctor entity..
The info varies according to each doctor and needs. To be filled out and altered by doctor from time to time.

DOCTOR’S VARIABLE’S
1) Specialty (General Practitioner of Other)
Name
Address
Telephone
Email of doctor
Email of Surgery for summary (Auto-Email Bookings ON/OFF)

1. Booking cycle Weeks=      1  2  3 4 (Some doctors have roster that repeat every week or 2 weeks or 3 weeks or 4 weeks, They need to select their repeating cycle)

Under each date the Doctor needs to select what section of times need to be booked as ‘On or Off’ and what booking periods they would like for various sections, ie 10 min or 15 or 20 or 30 or 60 minutes.

2. Black out Dates  =  They need to block out whole dates for days off or holiday period from date until date both dates inclusive this may be several weeks ahead.

3. Temporary changes of booking
This is to enable Doctor or Surgery to quickly block out time periods if doctor needs it for something else.

4. Information in Email Booking: This is the section sent along with their booking details stating conditions and messages that applies to that doctor and surgery, it may have details regarding Fees, cancellation fees etc

5. Blackout Booking period: A period of time that needs to be blocked out prior to appointment time. This frees up the surgery to give away times not booked on the internet to walk in appointments or use this period to fill it up with phone booking.
Also problem with time difference between different states within the country has to be taken into consideration, something I’m finding quite tricky to normalize.

So basically, my question is, which would be the best way to go about normalizing the above?. So as to create an appropriate schema/data dictionary and ERD. I’ve attempted a few with little success, but I’m sure you experts have a better idea then me. Getting the design aspect right would give me a great peace of mind.
Thanks in advance for all your help
ES
elusivesoulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
My basic design would be to put each 'thread' of data in a separate table.  For example, you have a list of doctors.  That's one table...doctor name, address, contact information, etc.  Then you have one for the patients.  For the scheduling, I would create your main table with all the available schedules.  This table can be altered by the individual doctors to adjust their schedule as they see fit.  Each block of time (15 min, 30 min, whatever length of time they use for 1 block) should be listed as a separate record.  So if a doctor has 5 blocks a day, you would end up with 35 records for scheduling availability for that doctor (5 blocks x 7 days).  The PK would obviously be an ID (autonumber would work), with an FK to the doctors table.  As patients select appointments and create a booking, this information goes into yet ANOTHER table, with patient ID, and block ID (the PK from the previously mentioned 'available schedules' table).

That would my initial design strategy.  I would start with that up to the functionality it allows, then start expanding.  If properly normalized, expanding into black-out dates and temporary scheduling changes should not be a big issue, since you'll only need to build on existing functionality.

Any questions?
0
elusivesoulAuthor Commented:
Hi Routinet,
Thanks for your help on this, all is much appreciated. I sort of do understand what your saying, and I have attempted to list below what you have recommended. However the schedule (and the creating another table) part is really confusing me. Please kindly assist me further on this.
Thanks
ES

tblDOCTORS
----------------
DoctorID (PK)
Specialty
Name
Address
Telephone
Email of doctor
Email of Surgery for summary

tblPATIENTS
----------------
PatientID (PK - Autonumber)
FirstName
LastName
DateOfBirth
Phone
Email


tblSCHEDULE
----------------
SheduleID(PK
Blockid

0
Mighty_SillyCommented:
Hey guys,

I tend to put things that are the same together, in this case, people.  If I were doing this I would put all the names in one table, regardless of doctor or patient.  Not knowing how widely use this application is going to be, but planning for expansion, what happen if a doctor is a patient of another doctor?  see what I mean?

That said, here's my rough out design, showing the important fields.  There would be more minor fields as the design is plan out more thoroughly, but it should give you an idea for now.

It is complex, but it's done w/ expansion in mind.  Obviously, it is something you'd want to discuss w/ your client to see if he forsee it expands to such level.

-tblPeople (PE_ID, first, last, mid/tie-breaker, DateIntoSys, DateModified)

-tblAddress - This allows people to have more than one address.  Some doctor practice out of various locations.
 (ADD_ID
  ADD_IDPE: foreign key from People
  ADD_IDLocation:  foreign to another table for Location:
                           Home, Work, Main Office, Branch office, etc.
  Address1
  Address2
  City
  Province
  etc...
  DateIntoSys
  DateModified)

-tblCommunication - This allows people to have more than one way of getting communications: Phone, cell, pager, e-mail, etc.
  COMM_ID
  COMM_IDPE: foreign key from People
  COMM_IDLocation:  see above explanation for Address
  COMM_IDType:  foreign key to CommunicationType table:  Phone, Cell, E-mail, etc.
  etc...
  DateIntoSys
  DateModified)

======== Above complete the General People Info gathering... below is Gut of the application.

-tblDoctor
 (DR_ID
  DR_IDPE: foreign key from People
  DR_IDTitle: foreign key from Title table for various specialties.
              A doctor could have more than one title or specialty, right? ;-)
  DateIntoSys:  when this record was entered
  DateModified: when this record was modified, assuming doc changes specialty, whatever.)

-tblDoctorOffice - this allows a doctor to operate out of more than one location
 (DO_ID
  DO_IDDR: foreign key from Doctor Table - NOT PEOPLE, since we now know s/he is a doctor
  DO_IDADD: foreign key from Address table
  DateIntoSys
  DateModified)

-tblDoctorOfficeCommunication - this allows doc to be notified by more than one type of technology
 (DOCOM_ID
  DOCOM_IDDR: foreign key from Doctor Table - NOT PEOPLE, since we now know s/he is a doctor
  DOCOM_IDCOMM: foreign key from Communication table
  DateIntoSys
  DateModified)

-Patient - repeat above three doctor tables for patient info.

The scheduling part is the most complex so if you thought the above Doc & Pat info were complicated... hahaha

I'd start with having lookup table for the 24 hours day and the 15min increment (two tables - one for hours, one for increment).  Alternatively, you can have just one table that breaks it down like 01:15, 01:30, 01:45, etc.  The bad thing about the second method is that you're locked into that system.  The first method allows changes at anytime.

-tblHour & tblTimeIncrement
-tblDayofWeek (I don't know how to tie things into the Calendar so that would be a piece for another Expert. Or, have some cool formula for Access to pick up automatically using Now()+days ahead.)

Now, create a Doctor schedule table with DoctorID, DayofWeek ID, HourID, & TimeIncrementID.  This allows doctor to "make their own schedule".

Then, for the actual scheduling table that patient sees when wanting to book an appointment - bring in the DoctorID and the corresponding "Doctor schedule table" ID, then for the Patient to select Day, Hour & TimeIncrement s/he wants.

Lastly, to see available time for a specific doctor, look in the above actual scheduling table for the Doctor ID and all the Day, Hour & TimeIncrements that are used.  What's left would be "Open" time slot available for new appointment.

Sorry, too long already.

-Silly-
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Steve BinkCommented:
My posted solution is kind of like the "... for Dummies" version of Mighty_Silly's.  While I would say the majority of users will not have the need for that kind of complexity, he does lend a very good example of normalization, and you could do much worse than using his model.

Both are expandable for the future...mine is more "slap-it-together-and-get-it-started-already" where Silly's approach is getting all the work done now in anticipation of future functionality.

For your scheduling table, I would actually recommend two.  One table for the doctors to create and administer their schedules.  This will provide you with THEIR list of their scheduling blocks, how many minutes per block, etc.  The second table would hold the appointments the patients have made.  If you have the doctors sign off on their schedules, say on a weekly basis, then you can use that table as a 'template' for the second, renewing it with fresh schedules each week.  Understand?
0
Mighty_SillyCommented:
Hey R,

Since we'll no doubt cross paths, Silly will do.  ;)-

-Silly-
0
Steve BinkCommented:
Don't worry, it's just an odd little quirk of mine.  Shane gets special treatment cuz...well, have you SEEN his name?   : þ
0
elusivesoulAuthor Commented:
Hi guys, sorry for the late reply. will be finalising this thing very soon. It's just been so tricky for me.
0
Steve BinkCommented:
Let us know if we can give you any more assistance with this.  
0
elusivesoulAuthor Commented:
Hi Silly and Routinet,
My apologies for the delay, as personal circumstances intervened over the past month. With regartds to the application, I'm having some trouble understanding some of the reasons for the tables

tblAddress: While it is possible to have more then one address, I dont think its that neccessary. i cant imagine someone wanting to place more then one address. The address for the patient is not as important as the address of the clinic. As it will be the patient that will be going to see the doctor, not the other way around. Maybe I've missed something here.
But I don't think a clinic will be mailing to 2 different mailing address's.

tblCommunication: This part is quite unclear to me, as the whole purpose of the website is for 'online booking'. So it can only be done online.

"-Patient - repeat above three doctor tables for patient info."

I'm not too sure as to what tables and which fields?.

" -tblHour & tblTimeIncrement
-tblDayofWeek (I don't know how to tie things into the Calendar so that would be a piece for another Expert. Or, have some cool formula for Access to pick up automatically using Now()+days ahead.)"

Maybe the above would be more suitable for asp?.

I think for some of the aboves, I'll need to do them in asp. As the  access application wont be used directly. But by patients via the net. So ASP is the only logical solution for this.
But I need to ensure the gut of the application is correct. Design wise. But I do understand the
purpose of the above is for future expansion, and I'm indeed in favour of that. I've done the sketch of the above db here..
www.w-projects.net/patientdb.zip
If you can take a look at it, and give suggestions, then that would be great.

Your help on this is so much appreciated Silly and Routinet.
Thankyou so much for your time and effort in helping me out on this.
ES
0
Steve BinkCommented:
tblAddress: This table would be used solely for the purpose of storing multiple addresses attached to a single patient or doctor record.  It does not matter WHO the addresses belong to, since you can differentiate between the two in the data.  If you intend on having one address for each patient, doctor, or clinic, you can eliminate this table.

tblCommunication: True, you are only using one method for booking the appointments, but what about communication between the clinic, doctor, and/or patient before or after the visit?  You can use this table to store various communication methods for just about anybody.  Say a clinic has more than one number (pretty common, yes?).  You can save all phone numbers for the clinic to be able to generate a "Contact Us" page.  If a patient has a home phone and a cell phone, and may be reached at both numbers, you save both numbers in the db for future reference.

Patient tables: While I think the tblCommunication table is a good idea, and the tblAddress table is usable also, I do not really see the need for you to have multiple location data for patients.  It is very unlikely you will need more than one address for any patient, since house calls are not part of your regimen (I am assuming..).  In any case, you can still use the Addresses table to store multiple locations, if need be.  I also believe you can combine the doctors and patients tables, and differentiate between them with data (such as a Yes/No field called "IsDoctor")

I would definitely recommend using ASP, since you'll need SOME way of getting the data back and forth to Access.  Access does not do online by itself...it's a database, not a web server.  Other options?  Any web programming language you desire.  I still recommend ASP, but that's because I'm familiar with what it can do and how to do it.  :)
0
elusivesoulAuthor Commented:
Thanks heaps routinet, the clarification makes more sense now. Also ASP is the only option because of the compatibility between it and Access in a windows environment, and i've used it a bit before.
0
Steve BinkCommented:
Good deal.  Let us know if you have any concerns, and don't forget to close your question.  :)
0
Mighty_SillyCommented:
Glad to see this is going somewhere.  I'll move back to be the observer and let big R be da driver.

-Silly-
0
elusivesoulAuthor Commented:
No worries, I will close it soon. Any comments on my db Silly?
0
elusivesoulAuthor Commented:
Thanks guys, you have been of great help. I only saw it as fair to split the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.