Simple relationship question

[Coyote]

I have a friend with a trucking outfit that needs a DB.

What I'm drawing a blank on in my rusty Access is this

ABC truck will transport two boxes. Info about the load is singular
in the record (weight size, etc)

They want up to two pickup and destinations. Box one gets picked up
at Bills and goes to Freds, box two gets picked up at Toms and goes to
Teds.

These three entities will be will be in tables

Carrier (truck)
Shipper (from)
consignee (to)

with the standard stuff of Name address phone etc.

so each record recorded would have....

Carrier, up to two Shipper, consignees, and load info.

I know it's simple, but it's been a few years

Thanks.

Dirk

 

[Allen Browne]

Coyote, when I hear a client saying "up to 2 pickups and destinations" and
"two Shipper", alarm bells go off. You can *guarantee* that 2 won't be
enough some day!

Typically, a shipper takes consignments from clients, and figures out what
truck to put them on later. One consignment may have multiple consignees
(possibly at different drop-off points). You therefore need these tables for
taking the initial booking:

1. Consign: ConsignID (pk), ShipperID, ConsignDate
2. ConsignDetail: ConsignDetailID (pk), ConsignID (fk), ConsigneeID,
LocationID, Description, Weight, DueDate

Now you come to matching up the actual trips with the promised consignments.
One trip can have several stops. At every stop, goods can be loaded and/or
unloaded. An any stop (loadpoint), multiple ConsignDetail records can be
loaded or unloaded. Any trip has at least 2 loadpoints (start, and
destination).

That means tables like this:

3. Trip: TripID (pk), VehicleID, DriverID
4. LoadPoint: LoadPointID (pk), TripID (fk), LocationID, Odometer,
ArriveTime, DepartTime.
5. LoadPointDetail: LoadPointID (fk), ConsignDetailID (fk), Direction
(loaded or unloaded).

That assumes various other tables, such as:
6. Vehicle,
7. Driver,
8. Location, and
9. Client (for shippers and consignees together).

If a ConsignDetail gets broken down to multiple trips you will need a bit
more.