473,395 Members | 1,701 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Scheduling Database - Design Help!


I need to develop a scheduling app and am having trouble with the
database
design. I can easily design a table hold appointments with start and
finish
times, but I always have an issue when it comes time to searching for
free
time.

The search examples:
Find the first available appointment in September
Find the first afternoon appointment
etc...

Should appointments be linked similar to a linked list? Should I create
a
row for each 5 or 10 or 15 minute slice of the day for every day and
then
just search for null in a 'used' field? This could grow way to fast.

If you need a more specific example to understand I can provide that,
but I
wanted to keep this as short as possible.

If anyone has experience designing a scheduling DB then please post
your
expoeriences.

Thanks

Sep 19 '05 #1
7 10257
Tr***************@gmail.com wrote:
I need to develop a scheduling app and am having trouble with the
database
design. I can easily design a table hold appointments with start and
finish
times, but I always have an issue when it comes time to searching for
free
time.

The search examples:
Find the first available appointment in September
Find the first afternoon appointment
etc...

Should appointments be linked similar to a linked list? Should I create
a
row for each 5 or 10 or 15 minute slice of the day for every day and
then
just search for null in a 'used' field? This could grow way to fast.


You could create a table TIMESLOTS holding all possible slots (start of
a x minute slice). You could then join it with the APPOINTMENTS table.

SELECT slot from TIMESLOTS, APPOINTMENTS
where not (TIMESLOTS.SLOT between APPOINTMENTS.STARTTIME and
APPOINTMENTS.ENDTIME)

You could add conditions on the time and on the people you want to
schedule a meeting for.

I'm still stuck on how to
- search for a continuous hour of free time
- search for something like "where 90% are free and what are the
appointments for the other 10% so that I could ask them to reschedule"

Daniel
Sep 19 '05 #2
CREATE TABLE available_times (appointment_date date, appointment_start
time, appointment_end time);

Put into this table one row per date per 5/10/15 minute slice. (This
isn't going to be that big- at 5 minute slices you've got 105,120 rows
per year).

CREATE TABLE appointments (appointment date, appointment_start time,
appointment_end time, <lots of keys to other tables depending on
structure>);

Then every appointment goes into appointments. To find free time, you
can do

CREATE VIEW free_times AS
SELECT appointment_date, appointment_start, appointment_end
FROM available_times
EXCEPT
SELECT a.appointment_date, a.appointment_start, a.appointment_end
FROM available_times a, appointments b
WHERE a.appointment_date = b.appointment_date
AND a.appointment_start BETWEEN b.appointment_start AND
b.appointment_end
AND a.appointment_end BETWEEN b.appointment_start AND
b.appointment_end;

and then add conditions to free_times to get first appointment in
September, etc.

HTH

Sep 19 '05 #3
Sorry, this was not tested. Only my idea.

CREATE TABLE Appointment
(id SMALLINT NOT NULL
,astart TIMESTAMP NOT NULL
,afinish TIMESTAMP NOT NULL
,person VARCHAR(20) NOT NULL
,telno VARCHAR(12) NOT NULL
)
SELECT b.afinish AS available_start
, TIME('00:00:00') + DEC(n.astart-b.afinish, 6,0) AS
available_duration
FROM Appointment b
, TABLE
(SELECT MIN(n.astart)
FROM Appointment n
WHERE n.astart > b.afinish
) n(astart)
WHERE TIME(:requested_duration) - TIME('00:00:00') >= (n.astart -
b.afinish)
-- * Find the first available appointment in September
-- AND MONTH(b.afinish) = 9
-- AND MONTH(n.astart) = 9
--
-- * Find the first afternoon appointment
-- AND b.afinish >= TIMESTAMP(CURRENT DATE, '12:00:00')
-- AND n.astart <= TIMESTAMP(CURRENT DATE, '23:59:00')

Sep 19 '05 #4
Create events for "open", "available for appointment", "not available
for anyone", etc. so that you have something for every time slot in the
work day. The events will be either scheduled or "schedule-able"; you
then have a routine carve out time slots within the "schedule-able".

Sep 19 '05 #5
1) Will be used more for storing information or for reporting on it?
2) Do appointments only start once every x minutes, or can they start
at any time?
3) Can more than one person schedule an appointment for the same time?

B.

Sep 19 '05 #6
I think you should stick with tracking the start and end dates only but
you will also need to track the available hours for appointments (M-F
9-5) so that you can do the subtraction and come up with the free time.

Sep 19 '05 #7

pb648174 wrote:
I think you should stick with tracking the start and end dates only but
you will also need to track the available hours for appointments (M-F
9-5) so that you can do the subtraction and come up with the free time.


I think I would use a start time and duration instead of start time and
end time. Then something like

table schedule
starttime timestamp
duration number (units of minutes?)
usedflag a code type (int or char)
location
description
....

free time are those rows with usedflag code indicating it's free.
Finding the first available free slot is something like

select min(starttime)
from schedule
where usedflag='free'
and duration >= :desired_duration

or all slots on a given day

select starttime
from schedule
where usedflag='free'
and starttime between :given_day and :given_day+1day

I'm really surprised this wasn't suggested earlier.
Ed

Sep 21 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Rushikesh | last post by:
I am designing a WEB BASED Accounting Software with ASP and SQL Server. For this I need some help for the Database design. My design is as follows. I)User table: User_id, UserName..... Users...
2
by: Trevor.D.Matthews | last post by:
I need to develop a scheduling app and am having trouble with the database design. I can easily design a table hold appointments with start and finish times, but I always have an issue when it...
2
by: Mike | last post by:
Hi I have been tasked with converting my pulp and paper mills weekly projected and actual contractor hrs excel spreadsheet into a an Access 97 database. So far my design has been to use a...
2
by: John C | last post by:
I am trying to develop a access database version 2002 from scratch and I am a novice programmer and need much direction. I have been researching and studying about relational database design and...
3
by: Alwin | last post by:
Hey All! I am currently designing a database model for (at first sight) a simple order entry program. The problem I'm currently facing is the exchange of data between the databases of each branch...
1
by: Timothy.Rybak | last post by:
I need help designing a database that keeps up with a point system we have here at work. I'll tell you what I know. First, I have a list of all the employee names for a table. Second, for...
2
by: cactuar | last post by:
Hi to all, i am currently doing a web application. I have a form that contains about 100 radio button and 100 checkboxes. my question is, how do you design it in the database table? should i make...
1
by: Brian Blais | last post by:
Hello, I am trying to design a system for people to submit a series of documents to a project. I want users to have the ability to submit updates to any documents, so that there should be a...
7
by: Jonathan Wood | last post by:
I've been given a spec that seems unwieldy to me. As I'm pretty new to DB design, I would appreciated any input this spec. The site has three types of users: ADMIN, RESELLERS, and CLIENTS. The...
7
by: MNNovice | last post by:
Please forgive me for this is a long request. I am designing a DB to capture expenses related to Federal Grants and to generate appropriate reports. Each grant starts with a Notice of Grant Award...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.