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.
| Author |
Topic |
|
motilok
Starting Member
24 Posts |
 Posted - 2010-08-25 : 10:58:55
|
| I need to create a database to keep track of our company vehicles. We have 18 drivers with various availability hours and 3 cars. So far I only got table DRIVERS with following columnsDriver_ID int,FirstName char(15),LastName char(15),Phone char(15),AlternativePhone char(15),StreetAddress char(50),City char(10),State char(10),Zip char(10),Notes char(100),TerminationDate datetimeI was thinking adding Drivers Availability table, but not sure what is the best way to go about it. And then I think I need one more table which would be filled up as drivers are assigned to vehicles, so that each vehicle is not assigned more than one trip at the same time and that same driver is not assigned to two different vehicles. Any suggestions on where should I start and how should I go about it? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-25 : 11:09:32
|
| Your description sounds fine. Finding "overlapping times" is a bit tricky.Driver Availability:Driver ID, Start Date+time, End Date+timeVehicle Assignment:Vehicle ID, Driver ID, Start Date+time, End Date+timeI'm assuming that Start / End Date+Time might span midnight. If not set a check constraint that DATE of both is the same. Either way, having the DATE, as well as the Time, in the Start/End columns will make it easier to check availability. |
 |
|
|
motilok
Starting Member
24 Posts |
Posted - 2010-08-25 : 15:53:46
|
quote:
Originally posted by Kristen
Your description sounds fine. Finding "overlapping times" is a bit tricky.Driver Availability:Driver ID, Start Date+time, End Date+timeVehicle Assignment:Vehicle ID, Driver ID, Start Date+time, End Date+timeI'm assuming that Start / End Date+Time might span midnight. If not set a check constraint that DATE of both is the same. Either way, having the DATE, as well as the Time, in the Start/End columns will make it easier to check availability.
I think I might need 2 Driver Availability tables, one for their available hours and another that will show their available hours after they are already assigned to make trips. Is the table bellow would be a good set up for overall availability?Driver_ID, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, AvailabilityStartDate, AvailabilityEndDateSample data:2002, 9:00am-9:00pm, 6:00am-9:00pm, 12:00pm-12:00am, 9:00am-3:00pm, 12:00am-11:59pm, 12:00am-11:59pm, 12:00pm-11:00pm, 09/01/2010, 03/25/2011 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-26 : 05:05:41
|
| "another that will show their available hours after they are already assigned to make trips"You can deduce / derive that from the Vehicle Assignments. If you have a separate table then they will get out of step, at some point! |
|
|
|
motilok
Starting Member
24 Posts |
Posted - 2010-08-26 : 10:25:16
|
| another question. what if I have a driver whose hours are 24/7 and will not change for a year. Can I put it in as StartDateTime 08/25/2010 12:00am, EndDateTime 08/24/2011 11:59pmhow can i derive this info in the query so that he shows up as available for trip on StartDateTime 9/01/2010 4:00pm EndDateTime 09/01/2010 9:00pm Or do I have to put his availability hours per one calendar day? |
|
|
|
|
|
|
|
|
Scheduling Database