On call table? whats the best way

[elite311]

Table design for on call schedule, whats the best way?

I'm working on a database for my company that shows service and delivery information and I'm trying to incorporate the on call schedule to the program. I made a table with the columns Market, Techname, Date, and want to use the calendar control to populate the Techname and Phone number for the market that is selected and what day is selected on the calendar using a query.

I know I can make this work but the problem I see is that my table will get HUGE with data if i have to enter the tech for everyday as there is 1 tech and 1 driver per market on call per every week per market with approx 100 markets. Just looking for some help on how to design something like this the best way.

Currently I have a table with all the techs listed and another table with the drivers and there info I was thinking maybe I could use those tables somehow to make an editable on call rotation table.

Thanks in advance

 

[MarkK]

You haven't really asked a question or provided enough specific information for someone to take a reasonble shot at solving something here.
If you present something more focussed I expect you'll get more useful responses. Keep in mind too that words like Market and Tech might mean very specific things to you and not to me.

 

[elite311]

Ok I see your point

So basically a Market is the territory the Tech/Driver covers. Tech does service calls after hours where the driver handles delivery issues after hours.

Basically I want to make a schedule for the after hours crew at the office to use and incorporate it into my current service info database. I have a form using the calendar control so the user can select a date and then depending on that date and market selected it shows who is on call for deliverys and service.

The problem is each tech/driver is on call for 1 full week at a time, and the only way I can think to make the schedule is have an entry for every tech/driver with the date in every entry. That would make a huge table because 100 tech/drivers x 5 days would make 500 entrys to do it this way. I'm trying to figure out how to set up a table so I can make the table only have 100 entrys so I can enter a date range and have the calendar control work. I also want to be able to plan out 4 weeks out at a time.

I have a table that is for techs and 1 for drivers and was thinking I could somehow add fields to it for the date range but sometimes a tech or driver switchs and is on call 2 or 3 weeks in a row etc etc.

I was thinking maybe I could make a table linked to the Market that has 4 weeks and I could select the 4 people in combo boxs or something. But I'm not sure how the best way to do this is so that the calendar control works too.

 

[MarkK]

First of all I wouldn't worry in advance about having too much data. I don't think you'll have significant performance issues until you're looking at millions of records, and at 500 records/day that'll take over five years.
I think you could make a schedule table that's structured something like this ...

[B]tSchedule[/B]
ScheduleID (Primary Key)
TechID (Foreign Key)
DriverID (Foreign Key)
MarketID (Foreign Key)
StartDate (The first date of the tech or drivers week on call)

You might need a function that rounds dates to the Sunday or the Monday on which the on-call situation starts. Something like ...

Function GetPreviousSundayFromDate(AnyDate As Date) As Date
[COLOR="Green"]'  Given any date, this function returns the date of the preceeding Sunday
'  If this function receives a Sunday, it returns the same date it received[/COLOR]
   GetPreviousSundayFromDate = AnyDate - Weekday(AnyDate) + 1
End Function

Make sure there is never data in both TechID and DriverID. Routines that update one of those fields should make sure the other is not used, since one record needs to describe the schedule for one person for one week in one market.
Is this helping? Is your thinking on this stuff being advanced here?