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.

ThanksOn 18 Sep 2005 22:20:03 -0700, Trevor.D.Matthews@.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.
>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

Hi Trevor,

Using pre-allocated slots would bloat your database (though Daniel's
idea would limit this somewhat), and at the same time, it would limit
the freedom of the user to make appointments the way he wants to, to the
granularity of your pre-allocated slots.

Here's an alternative:
CREATE TABLE Schedule
(PersonID int NOT NULL REFERENCES (Persons),
StartTime smalldatetime NOT NULL,
EndTime smalldatetime NOT NULL, -- but see below!
-- other columns,
PRIMARY KEY (PersonID, StartTime),
UNIQUE (PersonID, EndTime),
CHECK (EndTime > StartTime),
)
You'd also need to ensure that there are no overlapping intervals, but
that can't be done in a CHECK constraint - you'll ened a trigger to
verify that business rule.

With this design, you can go two ways:

a) Store only the appointments. If there are no intervals that start
before time Y and end after time X, then the time interval from X to Y
is available for appointments.
This approach makes the processes for adding, changing and removing
appointments easy, but makes searching for available time somewhat
harder, as you have to search for absence of rows.

b) Change the nullability of EndTime to allow NULLs. NULL will represent
"eternity". Define a code to represent available time. Give each person
one special starting row: StartTime is the earliest datetime your
application will allow; EndTime is NULL; row marked as "available time".
When making the first appointment, the end time in this first row is
changed to the start time of the appointment, a row is added for the
appointment and an extra row is inserted to makr the time from the end
of the appointment to eternity (NULL) as available.
This approach makes the processes for adding, changing and removing
appointments harder (think about the combinations: an appointment in the
middle of available time has to be treated differently from an
appointment that immediately follow the previous appointment, that's
followed by another appointment, or even both. When appointments get
removed, changed, or shortened, the time that is now available again has
to be collapsed with adjacent avaialble time. Etc etc), but makes
searching for available time easier, as each time will always be part of
exactly one time slot in the Schedule table.
Note: To be complete and fail-safe, you'll also have to implement checks
(in trigger code) to ensure that there are no overlaps and no gaps
between a person's rows in the schedule).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much to everyone for the input. I'll try out a couple of the
ideas and post back my results in case anyone wants to know how it
played out.

Thanks again,
Trevor

Trevor.D.Matthews@.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.
> 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

Scheduled Job Running MS Access in Background not Foreground

We had a scheduled job, within it a step would run an Access app. Before Access would pop up, and if it errors out it will shut itself down and the job would see it as an error and try and rerun the Access app.
After migrating to a new server (install is identical except for hardware), the Access app when called from a job will run in the background, meaning you can only see that it's running by going into the Task Manager and going into the Processes tab. Also
now when Access errors out, (in the background there is a dialog box indicating an error in Access and someone has to go and click ok, which can't be done, since Access is running as a background process) it will sit there waiting for someone to click OK
on the error dialog box. The scheduled job will just hang there since the job thinks Access is still running.
Does anyone know how to get the job to NOT run the Access app in the background and to know when Access errors out?
Can't you just write code to catch the error in the Access database?
"ckaneko" <anonymous@.discussions.microsoft.com> wrote in message
news:DB4CB092-B1B9-4388-9780-EFDCFC9052DB@.microsoft.com...
> We had a scheduled job, within it a step would run an Access app. Before
Access would pop up, and if it errors out it will shut itself down and the
job would see it as an error and try and rerun the Access app.
> After migrating to a new server (install is identical except for
hardware), the Access app when called from a job will run in the background,
meaning you can only see that it's running by going into the Task Manager
and going into the Processes tab. Also now when Access errors out, (in the
background there is a dialog box indicating an error in Access and someone
has to go and click ok, which can't be done, since Access is running as a
background process) it will sit there waiting for someone to click OK on the
error dialog box. The scheduled job will just hang there since the job
thinks Access is still running.
> Does anyone know how to get the job to NOT run the Access app in the
background and to know when Access errors out?

Scheduled Job Running MS Access in Background not Foreground

We had a scheduled job, within it a step would run an Access app. Before Ac
cess would pop up, and if it errors out it will shut itself down and the job
would see it as an error and try and rerun the Access app.
After migrating to a new server (install is identical except for hardware),
the Access app when called from a job will run in the background, meaning yo
u can only see that it's running by going into the Task Manager and going in
to the Processes tab. Also
now when Access errors out, (in the background there is a dialog box indicat
ing an error in Access and someone has to go and click ok, which can't be do
ne, since Access is running as a background process) it will sit there waiti
ng for someone to click OK
on the error dialog box. The scheduled job will just hang there since the j
ob thinks Access is still running.
Does anyone know how to get the job to NOT run the Access app in the backgro
und and to know when Access errors out?Can't you just write code to catch the error in the Access database?
"ckaneko" <anonymous@.discussions.microsoft.com> wrote in message
news:DB4CB092-B1B9-4388-9780-EFDCFC9052DB@.microsoft.com...
> We had a scheduled job, within it a step would run an Access app. Before
Access would pop up, and if it errors out it will shut itself down and the
job would see it as an error and try and rerun the Access app.
> After migrating to a new server (install is identical except for
hardware), the Access app when called from a job will run in the background,
meaning you can only see that it's running by going into the Task Manager
and going into the Processes tab. Also now when Access errors out, (in the
background there is a dialog box indicating an error in Access and someone
has to go and click ok, which can't be done, since Access is running as a
background process) it will sit there waiting for someone to click OK on the
error dialog box. The scheduled job will just hang there since the job
thinks Access is still running.
> Does anyone know how to get the job to NOT run the Access app in the
background and to know when Access errors out?

Scheduled Job Running MS Access in Background not Foreground

We had a scheduled job, within it a step would run an Access app. Before Access would pop up, and if it errors out it will shut itself down and the job would see it as an error and try and rerun the Access app
After migrating to a new server (install is identical except for hardware), the Access app when called from a job will run in the background, meaning you can only see that it's running by going into the Task Manager and going into the Processes tab. Also now when Access errors out, (in the background there is a dialog box indicating an error in Access and someone has to go and click ok, which can't be done, since Access is running as a background process) it will sit there waiting for someone to click OK on the error dialog box. The scheduled job will just hang there since the job thinks Access is still running
Does anyone know how to get the job to NOT run the Access app in the background and to know when Access errors out?Can't you just write code to catch the error in the Access database?
"ckaneko" <anonymous@.discussions.microsoft.com> wrote in message
news:DB4CB092-B1B9-4388-9780-EFDCFC9052DB@.microsoft.com...
> We had a scheduled job, within it a step would run an Access app. Before
Access would pop up, and if it errors out it will shut itself down and the
job would see it as an error and try and rerun the Access app.
> After migrating to a new server (install is identical except for
hardware), the Access app when called from a job will run in the background,
meaning you can only see that it's running by going into the Task Manager
and going into the Processes tab. Also now when Access errors out, (in the
background there is a dialog box indicating an error in Access and someone
has to go and click ok, which can't be done, since Access is running as a
background process) it will sit there waiting for someone to click OK on the
error dialog box. The scheduled job will just hang there since the job
thinks Access is still running.
> Does anyone know how to get the job to NOT run the Access app in the
background and to know when Access errors out?

Schedule a scripted restore on 2005 Express from .bak file.

Hi i have an web app demo that allows users to add info and change attributes within a SQL 2005 Express DB. I'd like to restore a clean copy of this database every couple of hours from a .bak file using a Windows scheduled task on the server. Has anyone got a .sql script for database restoration that i could use and call using a .cmd script file? Thanks.

Hi TheGrox,

Generally speaking the sql restoration script is like this:

RESTORE DATABASE [Your_databasename] FROM DISK = N'File_Path\BCKUP.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO

However, I don't think it can be used from a .cmd script file. To run .sql script file, you need to run it within sql server query analyzer (if you run it from a .cmd script, first it will jump up a window asking your to login to sql server, even though you enter the server name and user/password and login successfully, that sql script file will be opened there while won't be executed until you click the execute button from the GUI).

I think there are two solutions for you requirment. One solution is the easist, upgrade your sql express to a full vertion and use Server Agent-> Jobs. Run that script in a job and schedule the job to run at a sepcific time frequency. I believe it won't cost you more than 5 minutes;

The second solution is to use ado.net.Open your visual studio and create a new project, build a connection to your database first and then send the restore sqlcommand to it. Compile and build you project and after that, you create a windows schedule task and run that application regularly.

Hope my suggestion helps

|||

Hi, thanks for the suggestions. The running the .sql from a .cmd file isnt a problem, i already do that to automatically back up all DB's in my SQL Express instance by having a file called BackupDB.cmd with the following code:

CLS
ECHO OFF
ECHO Testing to make sure directories exist.
IF NOT EXIST C:\SQLBackups MD C:\SQLBackups

ECHO Complete with directory creation
ECHO **********************************************************
ECHO Backing up databases......
sqlcmd -S .\SQLEXPRESS -i c:\Windows\BackupExpress.sql -o C:\SQLbackups\backup.log
ECHO **********************************************************
ECHO Backup complete. Look in the C:\SQLBackups\Backup.log file for information.

ECHO ON

The BackupExpress.sql file is simply a script that loops through all databases on the server and creates a .bak file for each of them in the specified directory. Works nicely andmeans i dont need to reconfigure anything if a new DB is created, it is just automatically included in the backups.

So i guess i just need to create a Restore.sql script using the syntax you quoted and call it as above. The command runs under the context of a valid SQL user account specified when the scheduled task is created so login is not an issue.

The script for the BackupExpress.sql file if anyone is interested in using it is:

/**File Name: BackupExpress.sqlDescription: Backs up all databases. This script is mainly meant for SQL Express instancesThe script requires a C:\SQLBackups directory by default to backup to but can be changed with the @.OutputPath variable.Accompanying file is BackupExpress.cmd, which is used to schedule the script.**/SET QUOTED_IDENTIFIEROFF USE masterGOSET NOCOUNT ON DECLARE @.dayofweekvarchar(20)SELECT @.dayofweek =CASE datepart(dw,getdate())WHEN 1THEN'Sunday'WHEN 2THEN'Monday'WHEN 3THEN'Tuesday'WHEN 4THEN'Wednesday'WHEN 5THEN'Thursday'WHEN 6THEN'Friday'WHEN 7THEN'Saturday'ENDDECLARE @.OutputPathvarchar(500)DECLARE @.DatabaseBackupFilevarchar(500)DECLARE @.FolderNamevarchar(25)DECLARE @.DatabaseNamevarchar(25)DECLARE @.strSQLvarchar(2000)DECLARE @.hostnamevarchar(255)SET @.hostname = (select replace(convert(varchar(255),serverproperty('SERVERNAME')),'\','_'))SET @.OutputPath ='C:\SQLBackups'DECLARE cur_BackupCURSOR FOR select name fromsysdatabaseswhere name !='tempdb'OPEN cur_Backup-- Fetch the db names from CursorFETCH NEXT FROM cur_BackupINTO @.DatabaseNameWHILE@.@.FETCH_STATUS = 0BEGINSET @.DatabaseBackupFile = @.OutputPath +'\' + @.hostname +'-' + @.DatabaseName +'-' + @.dayofweek +'.bak'print @.DatabaseBackupFileSET @.strSQL ='BACKUP DATABASE '+@.DatabaseName+' TO DISK = "'+ @.DatabaseBackupFile+'" WITH RETAINDAYS = 1, NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'PRINT @.strSQLEXEC (@.strSQL)FETCH NEXT FROM cur_BackupINTO @.DatabaseNameEND-- Distroy CursorCLOSE cur_BackupDEALLOCATE cur_BackupSET NOCOUNT OFF