I will try.Can you give us a little more detail/example of what sort of things make up an order and Load?
- A driver calls in saying he needs to go from point A to point B with a load.
- Permit agent creates a new order
- The order states is marked as "New" meaning it has not yet been worked.
- Permit agent asks driver to verify truck number
- Permit agent adds the Truck, Driver and Trailer via the Assignment ID
- If a truck, driver, trailer doesn't exist, one must be created and properly assigned.
- If adding a truck, we'll need to get information like the truck number, year, make, model, number of axles, plate, plate state and vin (all this goes in the Trucks Table)
- If adding a trailer, we'll need to get information like the trailer number, year, make, plate, plate state, vin, trailer length, kinpin setting, and currently active number of axles and axle spacings ( all this goes in the Trailers Table)
- If adding a driver, we'll get his first and last name, phone and email
- The Assignments Table includes the TruckID, and the DriverID and the Trailer ID that truck is assigned to
- I created the assignments table that links all this together, but also included a field "Status" which tells us if the assignment is "Active" or "Inactive". So for example, in the rare case a new driver, truck or trailer is changed, a new record would be created in the Assignments table with the new change, and the previous record is mark "Inactive".... meaning, do not use for future orders because it is not the current assignment. I did it this way because I did not know of any other way to safely change a piece of the assignement, without altering the assignements of previous orders (hope that makes since)
- The agent may verify other information to make sure nothing has changed for the Assignments, such as the number of active axles (some axles on trailers can be dropped or raised in order to distribute weight properly for a load), different trailer, new truck.... etc...
- Agent gets load/equipment information from the driver. This information goes into the Loads/Equipment table and tells us a description of the load, the load make/model and serial. The Load relates to the Order and not the trailer because it's always something different.
- Agent gets the Starting Address and the Ending Address for travel (Where the load is picked up and will be dropped off)
- The agent determines the route later and what state permits will be needed for travel. (Although a the driver may request a specific route)
- The states needed are recorded in the order.
- The agent gets from the driver the overall width, length and height for the load with truck and trailer. (right now I have this in the Loads/Equipment table, but I'm thinking it more pertains to the order since it is an overall calculation)
- The agent gets from the driver the overall weight of everything that's recorded in the order.
- The call ends. The agent continues to add new orders as needed.
- Later, the agent goes back to view the order details.
- The agent goes back to the order cue.
- The agent reviews all the new orders.
- The agent pulls up a map to help determine the best route for a specific order.
- The agent goes to each state website to review travel restrictions and requirements.
- If needed, the agent will order a police escort and/or pilot cars
- THe agent submits an order to each state to obtain a permit for travel
- Once all states are submitted, the order status is now "Submitted"
- Once the permits come back, they are uploaded to the database (right now I plan on attaching these files in the StatePermits table)
- The StatePermits table includes an OrderID field, so we know which order has what states. (There can be one to many state permits so this seemed to be the best way to do this)
- The permit fee is recorded in the StatePermits table as well as our service fee for each state.
- If pilot cars or escorts are needed, they prices only, will be recorded in the ORders Table. In paperwork pertaining to these two will be uploaded and attached to the order as well.
- I believe the permits are sent out (emailed or faxed to the driver) as soon as they are available, I'll have to double check.
- Once all permits are received, and the driver has all permits, the order is marked "completed"
- After this, the agent has to "Invoice" the order. That is as soon as the order is complete or when she is able, she sends a copy to the Trucking Company's dropbox.
- The order is marked "Invoiced" and no further action needs to be taken.
Some extra information, our company is just two people, me (sort of working as IT, Finances and now Database Admin) and my wife that does everything else to communicating with drivers, getting permits, etc.
We are only contracted to work for one trucking company. We don't expect to work for any others, but there could be a possibility. If we were to take on another trucking company, we'd need to start hiring other employees to act as permitting agents.
So if I were to sum this down I'd say the following....
An order will show it's status, it's Assignment (Truck, Driver and Trailer from the Assignments Table) it's Load(from the Load's Table), Permits needed for each state (from the StatePermits table), Overall Width, Length and Height, Overall Weight, Paper work for any Pilot Cars or Police Escorts (State Permits are attached to the StatePermits Table), PermitFee Total, ServiceFee Total, PilotCarCost, PoliceEscortCost, AdminCost(for any other fee), and then Grand Total Cost of everything.
Another note, driver some times call to see how much a particular past order was. Or might need to quickly pull up an order to resend a permit that was lost or didn't receive.
I might be missing something, but that should be a good bit of the process. Sorry it's so lengthy, but it's how I could best describe the process.