I was just assigned a task to complete and I need serious help in the db structure/design.
Our company employees techs to perform tasks at client's locations. all of these tasks are flat rate based and they are allocated time blocks in 30 minutes interval, hence the confusion in db design.
I have created these tables with this structure.
EmpID -->(int PK)
EmpScheduleID (int FK to scheduletable)
EmpLname and so on
ScheduleID (int PK)
--rest of week days --
SkuID (int PK)
Durration (int) -->should represent the 30 minutes blocks as in 1=30 minutes, 2=60 minutes
CallID (int PK)
ClientID (FK to Clientstable)
SkuID (FK to Skutable)
EmployeeID (FK to employeetable)
CallDate (smalldatetime) something like 12/04/2005
StartTime (smalldatetime) something like 1:30 PM
I can not figure out how to implement a time slicing table and have it linked to the schedule table in order to get a calendar like representation of the schedule.
I have seen this post which includes a very similar situation but due to my lack of DB design I can not understand building the structure of the tables based on the last posting http://www.webservertalk.com/archive289-2005-9-1206387.html
Any ideas would be greatly appreciated even if they including changing the database structure completely.
Thanks in advance