Results 1 to 4 of 4
  1. #1
    aurorist is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    2

    Truck transportation business orders

    Hello,

    I am trying to design a database for a transportation business which offers tranportation of goods from point A to B with its own trucks/trailers. I've just started. My tables so far:

    Code:
    orders (orderID, clientID, truck_driverID, truckID, trailerID, type: national/ international, date started, date ended, freight load place, freight unload place, place where freight got loaded to another trailer, amount of payment received, IF international then also:  shipID, price of ticket, date, booking number, truckID[may or may not exist], truck_driverID, trailerID, trailer_weight)
    order_status (finished, on route, ended)
    clients (business name, address, phone number)
    truck_drivers (first name, last name, phone number)
    trucks (truck number, description)
    trailers (trailer number, notes)
    ships (shipping company name, booking number, ticket price)
    There are times, when the trucks, drivers or trailers change (also cargo gets moved from one trailer to another sometimes and drivers/trucks change) while completing one order. When they use overseas shipping they don't always use that same truck to finish the job. What would be the best solution to manage these kind of situations, without creating unnecessary records?

    They also deal with national and international transportation. In those latter cases there are extra fields that need to be filled because they use overseas shipping (e.g. shipping company name, booking number, weight, trailer nr on ship). Should I make an entirely new table for international orders or put it all in one table (like I've done up here).

    So far I've found these two data model examples






    and I've had little help from them since I don't really want to deal with addresses.

    I would like to get this main structure of the db right so I wouldn't have to make any major changes later. I've recently finished Lynda access 2007 essential training and I've learned some of it in school (still learning), but I don't have much experience yet. Any help would be greatly appreciated and sorry for my English

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    Depends how much history of each shipment you must retain. If you need to know all the trailers, trucks, drivers, ships that interacted with each shipment, then will need table(s) to store a record for each transfer.

    The additional fields for international shipments could be in a child table or keep in the parent table and live with all the blank fields for the domestic records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aurorist is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    2
    Thank a lot for your reply June7 and yes I must retain all shipment history. So it would be best to create seperate tables for each of the entities? In that case I'm not sure if I understand the relationships that should be implemented with the parent table if at all. Can you maybe point me to an example db or something like that. I don't have a lot of experience and I just want to make sure that I get it right.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    A separate junction table for each entity would be the most normalized. This would mean a new record each time resource (truck, ship, driver, trailer) was changed. Instead of fields in Orders for the resources, related tables would be like:

    OrdersTrucks
    OrderID (foreign key)
    TruckID (foreign key)
    DateAssigned

    Order_Status has only the 3 fields? Date values? This table could be combined with Orders.

    I have never worked with a db like this. Your search was probably as good as I could come up with.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. School bus transportation
    By IsiEMT in forum Access
    Replies: 3
    Last Post: 12-11-2011, 11:01 AM
  2. Can access analyze orders with a SKU list?
    By shubhamgandhi in forum Programming
    Replies: 3
    Last Post: 08-08-2011, 01:12 PM
  3. Purchase orders 2 in 1
    By jordanturner in forum Forms
    Replies: 1
    Last Post: 09-09-2010, 03:38 AM
  4. Table Without PK Switching My Index Orders
    By ajetrumpet in forum Access
    Replies: 5
    Last Post: 09-07-2010, 06:11 PM
  5. Orders & Products
    By mastromb in forum Database Design
    Replies: 4
    Last Post: 01-22-2010, 07:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums