Set up table for appointments diary

[sean2510]

Hi
I have browsed the web and this forum but cant seem to find a solution to my question. (although there are many questions on the same topic).

Basicly, I am building a customer appointments database. The end result will be a system where the user can book/delete/amend appointments (with pre set time 15 minute slots) and by clicking on the name in a particular slot be able to view the client record details. There will need to be 4 seperate 'diarys' seen side by side.

I am happy with the client record details bit, however my queston is this.

What would be the best way to set up the table that holds the appointment slots? I need to be able to see the name of the cliet in a particular slot, and the reason for the appointment.

The only way i can see is to have 4 tables (one for each diary)
each table would have a new row for each appointment date and time. For example row 1 would be 01/06/2011 at 08:00. Row 2 would be 01/06/2011 at 08:20.

The problem with this is that there would be 36 time slots (rows) per day. This is going to result in a massive data table and the problem of manualy entering each date/time in advance.

There must be a better way to set up the tables for apointments? Do any of you have any suggestions? I have searched for examples with no luck, and have considered outlook, but decided this would not be suitable.

So, help. I know setting up an appintments diary is a big, complicated task, so i want to have the most effective way to hold my data.

any suggestions appreciated.

Sean

 

[gmmastros]

The only way i can see is to have 4 tables (one for each diary)

Don't do this. You will eventually regret it. Instead, add another column to the table to store the "Diary Number" or some other column name that differentiates each diary.

Personally, I would suggest that you forget about the 15 minute time slot requirement while designing the table. Also, do not store rows where all of the data (except the time) is blank. This is wasteful.

I would suggest this:

AppointmentId. This would be an integer column (4 bytes), and would be unique for each row in the table.

DiaryNumber. This would represent the "1 of 4" diaries. I would use a tinyint for this column. (1 byte)

ClientId. I assume this would be an integer that corresponds to your client. This value would store whatever your primary key is for the client table. If this is an integer, it requires 4 bytes.

AppointmentStartDate. This would be a Small Date Time column (4 bytes) and would represent the time the appointment is scheduled to begin.

AppointmentEndDate. Another SmallDateTime (4 bytes) that represents the time the appointment ends.

If these are your columns, each row in the table would take 13 bytes. This is extremely efficient in terms of storage. Looking at it another way, you could store approximately 1000 rows in 13 kilobytes. You could store 1,000,000 appointments in 13 megabytes. With today's memory and hard drives, this is nothing in terms of storage. You'll want to have several different indexes on this table to speed up your queries so that 1,000,000 appointments may take 30 megs or so to store it. This is still extremely small.

The benefits of this approach are:

1. If you later decide that you need another diary (a fifth one), all you need to do is store another value in your column. You won't need to change the database or the queries, it will just work.

2. If you later decide to add additional functionality, you'll only need to modify one table. For example, suppose you wanted to add a Notes column to the table. Modifying one table and a couple of queries will be a lot easier than modifying 4 tables.

3. If you later decide to schedule in 10 minute (or 20 minute) increments, again, you won't need to modify the table or the queries.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sean2510]

Thanks that makes sence.

The only concern i would have is that the end user needs to be able to go so a specific date and time, and see that the slot is free. and book in an appointment. this could be 6 months in advance. They would not be happy about creating a new appointment for this, as there time is limited and it needs to be user friendly.

Can you see away around this?

 

[gmmastros]

Well... the end users shouldn't be using SQL Server Management Studio to view appointments. Instead, there needs to be a front end application for this. This could be a client/server app or a web site that you build. Either way, this front end application would have an interface where the user could pick a date (from a calendar) and then the app would show the appointments for that date.

This app would simply need to query the database for the appointments on that day and display them. The front end app would be responsible for the 15-minute time slots.

Front end apps should be user friendly, not databases.

If you need help building queries, just ask.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sean2510]

the front end will be a access form. Sorry i did nt make myself clear.
SG