Hours database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Hours database

Author

Topic

mcalsada
Starting Member

1 Post

Posted - 2004-05-31 : 14:50:28

Does anyone have suggestions for designing a database that will hold hours of operation.

Specs:
Multiple locations will have different time ranges (5/1/04 - 8/30/04 Summer Session, 9/1/04 - 12/15/04 Fall, etc.) Each location will have normal hours of operation (M-TH 8am-4pm, F 9am-2pm, Sat 12 - 7pm, Sun Closed, etc.) and there will also be exceptions. both for closures and time adjustments (which may include a range of dates). e.g. 12/25 Closed - Christmas, 4/1/05 - 4/10/05 Closed Spring Break, 5/31/04 10am - 5pm Memorial Day)

Any ideas on how you would design such a database?

Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-31 : 19:20:59

You should be able to do it in 2 tables:

Date Range Table
-----------------
RangeID int ID
DateFrom datetime
DateTo datetime
IsNormal bit
OpenTime decimal (or datetime)
CloseTime (ditto)

Normal Operation Hours
---------------------
DayOfWeek int
OpenTime datetime
CloseTime datetime

The date range table holds the ranges for the seasons, and also for the special days (in which case the start date and end date are the same.
The IsNormal bit flag indicates that the date range in question is to use the Normal Operation Hours table. The DayOfWeek int field would be 1-7, and would match the number returned from using the 'w' flag in DatePart.
If not, the OpenTime and CloseTime values are used from the Date Range table.

This could be extended to having different 'normal' hours for different seasons - all it would mean is adding a SeasonID to both tables.

HTH,

Tim

gates_micro
Starting Member

29 Posts

Posted - 2004-06-01 : 05:19:19

Location Table

LocationId (pk) int
LocationName varchar

Range Table

RangeId(pk) int
LocId (fk->locationId (LocationTable)) int
SessId(fk->sessionId(SessionTable)) int
OpId(fk->opsId(OpsTable)) int

SessionTable

SessionId (pk) int
SessionName varchar
StartRange datetime
EndRange datetime

OpsTable

OpsId (pk) int
StartTime datetime
Closetime datetime
Exception datetime

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources