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