Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to structure tables to allocate transport services?

Status
Not open for further replies.

Werneck

Technical User
Aug 5, 2003
36
0
0
BR
Hi,

How to struture tables to allocate transportation resources to clients?

This an internal transportation service: internal clients call in advance to schedule a service with a certain type of vehicle on a certain date.

At the last minute, the dispatcher will assign the avaiable vehicle number of that type and an avaiable driver name. Might also change the vehicle type.

Could some one give me an idea on how to start structuring this database or refer me to some existing model or basic application?

Thank you
Cesar Werneck
 
Cesar,

Sounds quite straight forward really, the key is looking for the separate entities from your example...

This an internal transportation service: internal clients call in advance to schedule a service with a certain type of vehicle on a certain date.

At the last minute, the dispatcher will assign the avaiable vehicle number of that type and an avaiable driver name. Might also change the vehicle type.


So from this we get the following...

tblVehicle
VehicleID (PK)
Type (FK)

tblVehicleType
Type (PK)

tblClient
ClientID (PK)
Name
Dept

tblDriver
DriverID (PK)
Name

tblBooking
BookingID (PK)
Date
ClientID (FK)
VehileID (FK)
DriverID (FK)

Probably not exactly how it will look in terms of table attributes, however there or thereabouts.

Hope this helps.


Leigh Moore
LJM Analysis Ltd
 

Leigh,

Your suggestion works fine!

How to avoid booking either same vehicle on the same time (or time interval) and/or same driver on the same time (or time interval)?

I inserted these two conditions on tblBooking via 2 indexes (DateBegin + DateEnd + VehicleID and DateBegin + DateEnd + DriverId). DateBegin and DateEnd are Date/Time. Each one works fine but only if there is a complete coincidence of DateBegin and DateEnd, so a vehicle or a driver can still be wrongly assigned say from 10:00 to 12:00 and from 11:00 to 13:00 of the same date.

I saw on some applications a detail table, like a BookingDetail. Do I need some thing like this and what for?

Thanks for some more input.
Cesar Werneck
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top