How to structure tables to allocate transport services?

[Werneck]

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

 

[SQLBI]

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

http://www.ljm-analysis.co.uk

 

[Werneck]

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