doctors' appointments - please advise

i am developing a clinci software and i guess i reached to the most difficult part of it which is the appointment module.. I have made it as explaied below but not sure if i am doing it the right way or if i need to redesign my tables' structure.. please advise..

i have create doctors' duty table:

USE [shefa]
GO
/****** Object: Table [dbo].[staff_duty] Script Date: 11/16/2006 02:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[staff_duty](
[duty_id] [int] IDENTITY(1,1) NOT NULL,
[staff_file_no] [int] NULL,
[staff_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[duty_from] [datetime] NULL,
[duty_to] [datetime] NULL,
[record_locked] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_staff_duty_record_locked] DEFAULT ('N'),
[created_date] [datetime] NULL CONSTRAINT [DF_staff_duty_created_date] DEFAULT (getdate()),
[created_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_pc] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_version] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_domain] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_os] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created_workingset] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_staff_duty] PRIMARY KEY CLUSTERED
(
[duty_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

and created the following appointments table:

USE [shefa]
GO
/****** Object: Table [dbo].[appointments] Script Date: 11/16/2006 02:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[appointments](
[appointment_id] [int] IDENTITY(1,1) NOT NULL,
[appointment_guid] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [DF_appointments_appointment_guid] DEFAULT (newid()),
[appointment_file_no] [int] NULL,
[appointment_telephone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_dr_id] [int] NULL,
[appointment_dr_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_start] [datetime] NULL,
[appointment_end] [datetime] NULL,
[created_by_date] [datetime] NULL CONSTRAINT [DF_appointments_created_by_date] DEFAULT (getdate()),
[created_by_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated_by_date] [datetime] NULL,
[updated_by_user] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_appointments] PRIMARY KEY CLUSTERED
(
[appointment_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

now i will need in C# to list the doctor's free time (means should be between his from and to in the staff_duty table and not listed in the appointments table for the dame day).. keeping in mind that each appointment should NOT take more than 15minutes.

Jassim:

I put together a mock-up based on what I could see. The main thing that I can see is the need for a "time slot" table. Also, you might have need for a "calendar" table.

declare @.staff_duty TABLE
(
[duty_id] [int] IDENTITY(1,1) NOT NULL,
[staff_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[duty_from] [datetime] NULL,
[duty_to] [datetime] NULL
)

insert into @.staff_duty(staff_name, duty_from, duty_to) values ('Ben Casey', '9:00', '17:00')
insert into @.staff_duty(staff_name, duty_from, duty_to) values ('Mark Welby', '8:00', '16:00')
--select * from @.staff_duty

declare @.appointments TABLE
(
[appointment_id] [int] IDENTITY(1,1) NOT NULL,
[appointment_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[appointment_dr_id] [int] NULL,
[appointment_start] [datetime] NULL,
[appointment_end] [datetime] NULL
)

insert into @.appointments (appointment_name, appointment_dr_id, appointment_start, appointment_end)
values ('Scooby Doo', 1, '12/1/6 8:45', '8:58')
insert into @.appointments (appointment_name, appointment_dr_id, appointment_start, appointment_end)
values ('Wiley Coyote', 1, '12/1/6 9:00', '9:20')
insert into @.appointments (appointment_name, appointment_dr_id, appointment_start, appointment_end)
values ('Betty Rubble', 2, '12/1/6 10:00', '10:13')
insert into @.appointments (appointment_name, appointment_dr_id, appointment_start, appointment_end)
values ('Wilma Pebble', 2, '12/1/6 13:15', '13:29')

print ' '
print ' -- Appointments: -- '
print ' '
print ' '

select appointment_id,
left (appointment_name, 25) as appointment_name,
appointment_dr_id as dr_id,
convert (varchar (20), appointment_start, 100) as appointment_start,
convert (varchar (20), appointment_end, 100) as appointment_end
from @.appointments

declare @.baseTime datetime set @.baseTime = ''

-- --
-- A "Time Slot" table. It seems like the need to deal with
-- specific "time slots" is very high in an "appointment
-- tracking" application.
--
-- I used the master.dbo.spt_values table as a iteration utility
-- for burning through all of the necessary time slot. This
-- table should NOT be used as a basis for day-to-day production
-- code because the contents of this table is not guaranteed.
-- Indeed, the number range differs between SQL Server 2000 and
-- and SQL Server 2005, so be aware!
--
-- If you have a consistent need for a table as an iteration
-- utility, consider article:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
--
-- It might also be a good idea to create a time slot table in
-- combination with a calendar table. A good article for
-- a calendar table is at:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
--
-- --
declare @.timeSlot table
( slot_id integer not null,
fromHourMin integer not null,
toHourMin integer not null,
fromDt datetime not null,
toDt datetime not null
)

insert into @.timeSlot
select number+1,
100*(number/4) + 15*(number%4),
100*((number+1)/4) + 15*((number+1)%4),
dateadd (mi, 15*number, @.baseTime),
dateadd (mi, 15*(number+1), @.baseTime)
from master.dbo.spt_values (nolock)
where name is null
and number <= 95

declare @.dr_name varchar (255) set @.dr_name = 'Mark Welby'

print ' '
print ' -- '
print ' -- Available Time Slots: -- '
print ' -- '
print ' '

select d.duty_id,
left (d.staff_name, 25) as staff_name,
right (convert (varchar (30), s.fromDt, 100), 7) slotTime
from @.staff_duty d
inner join @.timeSlot s
on d.duty_from <= s.fromDt
and d.duty_to >= s.toDt
and d.staff_name = @.dr_name
and not exists
( select 0 from @.appointments a
where convert (datetime, convert (varchar (30), appointment_start, 108)) <= s.fromDt
and convert (datetime, convert (varchar (30), appointment_end, 108)) > s.FromDt
)
order by d.staff_name,
s.fromDt

--
-- Sample Output:
--

--
-- -- Appointments: --
--

-- appointment_id appointment_name dr_id appointment_start appointment_end
-- -- - -- -- --
-- 1 Scooby Doo 1 Dec 1 2006 8:45AM Jan 1 1900 8:58AM
-- 2 Wiley Coyote 1 Dec 1 2006 9:00AM Jan 1 1900 9:20AM
-- 3 Betty Rubble 2 Dec 1 2006 10:00AM Jan 1 1900 10:13AM
-- 4 Wilma Pebble 2 Dec 1 2006 1:15PM Jan 1 1900 1:29PM

-- --
-- -- Available Time Slots: --
-- --
-- duty_id staff_name slotTime
-- -- - --
-- 2 Mark Welby 8:00AM
-- 2 Mark Welby 8:15AM
-- 2 Mark Welby 8:30AM
-- 2 Mark Welby 9:30AM
-- 2 Mark Welby 9:45AM
-- 2 Mark Welby 10:15AM
-- 2 Mark Welby 10:30AM
-- 2 Mark Welby 10:45AM
-- 2 Mark Welby 11:00AM
-- 2 Mark Welby 11:15AM
-- 2 Mark Welby 11:30AM
-- 2 Mark Welby 11:45AM
-- 2 Mark Welby 12:00PM
-- 2 Mark Welby 12:15PM
-- 2 Mark Welby 12:30PM
-- 2 Mark Welby 12:45PM
-- 2 Mark Welby 1:00PM
-- 2 Mark Welby 1:30PM
-- 2 Mark Welby 1:45PM
-- 2 Mark Welby 2:00PM
-- 2 Mark Welby 2:15PM
-- 2 Mark Welby 2:30PM
-- 2 Mark Welby 2:45PM
-- 2 Mark Welby 3:00PM
-- 2 Mark Welby 3:15PM
-- 2 Mark Welby 3:30PM
-- 2 Mark Welby 3:45PM

|||

that's a bit complicated...

let me ask the following question..

if I have the duty_from and the duty_to form the staff_duty table..

how can I create a procedure to generate an INSERT statement to insert to timeTable start_date based on 15minutes?

|||I too am trying to develop an appointment app. What development tools are you using? Will you be connecting to SQL Server?