Normalization Issues - Please help
Posted on 2004-11-04
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.
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.
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.
1) Specialty (General Practitioner of Other)
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