Need suggestions please (1 Viewer)

[Glytch]

I'm creating a database for a freight company to keep track of truckloads that they have or may dispatch drivers to. The data is split across many tables right now tblOrders (where the load detail is going), tblCustomers (companies that are looking to receive loads), tblCarriers (companies that move the loads). There are also tables for phone numbers and addresses but they are not important for right now.

The orders table (tblOrders) has fields for miles, hours, pieces, and pallets all of which could be the basis for how the customer is charged or the carrier is paid. The problem is that these sometimes a carrier will calculate their miles differently than the customer, when this happens I need to store both of those values. Here is an example: ABC Company tells us they want to move some of their product from pointA to pointB which is 100 miles. We then contact a carrier for the load, but the carrier claims that the distance between pointA and pointB is 110 miles (not all mapping software is exact in their mile calculations).

I'm fairly certain that I need to move the miles, hours, pieces, etc to a new table, but I'm not sure the best way to link the data to each load so that it will still display on the form.

If I were to create a new table called tblOrderDetails to contain the values for miles, hours, etc as well as some yes/no fields for "Carrier_Details", "Main_Details", "Fleet_Details"(I didn't get into fleet but it's the same concept as carriers, the data could be different) how would I update or even display the data on the main form?

I've been trying to figure out a way to

 

[Glytch]

Here is what I'm thinking would work, but I'm not sure if it's the best way to handle this.

tblOrders
-LoadID (PK) _Autonumber
-FromCompanyID (FK - tblCustomers on CompanyID)  _Number
-ToCompanyID (FK - tblCustomer on CompanyID) _Number
-OrderTakenBy _Text
-CallFirst _Yes/No
-Hazmat _Yes/No
-Notes _Memo

tblOrderDetails
-OrderDetailID (PK) _Autonumber
-LoadID (FK - tblOrders on LoadID) _Number
-Miles _Number
-Pcs _Number
-Skids _Number
-Hours _Number
-IsMain _Yes/No
-IsCarrier _Yes/No
-IsFleet _Yes/No

I want the main order form to have these fields required so whenever someone calls us we put in their miles/hours/etc and the data would go into the order details table and check yes for 'IsMain'.

On that same form I will have a tab control for carrier or fleet, whenever someone goes to that tab they will have the option to enter different values for miles/hours/etc and it would select either 'IsCarrier' or 'IsFleet' and insert the record.

Could someone help me out with how to accomplish this? I'm not sure how to insert or even display the records right now.

 

[CraigDolphin]

The estimate of miles, hours etc depends on the combination of order and customer, or the combination of order and carrier?

To me that suggests you might want something like

tblOrders
-LoadID (pk)
-OrderTakenBy _Text
-CallFirst _Yes/No
-Hazmat _Yes/No
-Notes _Memo

tblCustomers
CustomerID (Pk)
etc

tblCarriers
CarrierID (pk)
etc

tblOrderCustomer
OrderCustomerID (auto pk)
LoadID (FK)
CustomerID (FK)
OrderCustomer_MileageEst
OrderCustomer_HoursEst
etc

tblOrderCarrier
OrderCarrierID (auto, pk)
OrderID (FK)
CarrierID (FK)
OrderCarrier_MileageEst
OrderCarrier_HoursEst
etc

Then, create a form based on tblOrders with the relevant fields and two subforms.

The first subform based on tblOrderCustomer, and in single-form view, with all the relevant fields from that table.

The second subform based on tblOrderCarrier, in single-form view again, again with all the relevant fields.

That way, you can see the relevant data from each side of the equation. And you can make all fields required in the junction tables so that when you have information from the carrier you must enter all the information for the carrier but you are not required to simulataneousaly enter any data from the customer side (you may have to wait for one side to provide data).

Also, this will save your bacon if ever two customers decide to jointly order a load since the design allows for multiple customers or carriers per load. If you want to enforce only one customer or carrier per order, this could be a rare ionstance where a one-to-one relationship might be useful (just set the LoadID field in the two junction tables to not allow duplicates)

Or alternatively, just leave all the fields in tblOrders. Have a field for each type of estimate and display only the relevant fields on a tab control.

Whatever floats your boat

 

[Glytch]

At this point in the databases development it would be difficult for me to change the foreign keys in tblOrders. I already have a few forms based off of tblOrders which contains the associated company/carrier for each load. I may still take your suggestion and make those changes, but I would like to do this without having to go back and recreate some other forms if not needed.

What does this method offer that mine does not? It appears that they are very similar. Your suggestion would require me to create 3 new tables (customer, carrier, fleet) as opposed to one table. In both methods the tables would store the same information but with a single table I could simply add a field(s) to specify if that record is tied to the customer, carrier or fleet.

Or alternatively, just leave all the fields in tblOrders. Have a field for each type of estimate and display only the relevant fields on a tab control.

This wouldn't work without creating an enormous amount of extra fields. Customers and carriers will only be billed/paid in one way, while fleet can be paid many ways. I didn't go into fleet pay before but fleet pay can be based off of miles, hours, flat rate and many other variables at the same time. So that data cannot be stored in the orders table.

 

[CraigDolphin]

At this point in the databases development it would be difficult for me to change the foreign keys in tblOrders. I already have a few forms based off of tblOrders

Precisely why you should figure out your table structure before devleoping everything that depends on them.

What does this method offer that mine does not?

Comparatively simple form/subform dataentry interface, without needing to code too much vba, springs to mind immediately.

I think it's also going to be a lot simpler to do comparisons of the two kinds of 'estimate information' via queries with the structure I proposed.

Also there's no need for fields like 'Is_Carrier' in my scheme. A customer is not a carrier. A carrier is always a carrier. The difference is implied by the table itself.

Does 'Fleet' depend on whether a specific carrier is also a fleet operator/member? If so, rightly that field belongs in the Carrier table. You enter and store it only once per carrier instead of for each order that carrier is involved with. You can lookup whether the carrier is fleet in a query or form any time you need to see that information.

And, as I mentioned before, there's the flexibility to deal with multiple customers, or multiple carriers, per load if ever that situation arises without a complete redesign of all those lovely forms and queries and code etc.

And since I'm not in your line of work, I can't really comment as to some of those other fields since I don't know how they really relate to the entities you're tracking.

Regarding the pay, that comes down to a formula that I can't possibly comment on

This wouldn't work without creating an enormous amount of extra fields

Agreed. Which is why I suggested the other option first

Remember, I only offered the advice because you asked for input. If you don't like my advice, then feel free to reject it and keep asking for other solutions. Maybe someone else will help you to make your data model functional without rebuilding from scratch.

 

[Glytch]

Remember, I only offered the advice because you asked for input. If you don't like my advice, then feel free to reject it and keep asking for other solutions. Maybe someone else will help you to make your data model functional without rebuilding from scratch.

I very much appreciate your advice thus far! I think you may of misunderstood my last post, I was just trying to find out if I can do this without modifying my previous forms and vba.

Your method does make more sense for those reasons you listed, but I think there could be a compromise. The database does not need to accommodate the option of multiple customers or carriers per load like your method allows. So it seems that I could keep my order table how it is now and still create the tables you suggested to get a very similar result. Instead of my single table with the yes/no fields I could create 3 tables for each option while still keeping the customer and carrier data in the orders table. Do we agree here?

I didn't go into fleet very much because it is kind of complicated. The fleet option is for whenever my company decides to move a load rather than hiring a carrier to do it. We offer our fleet numerous pay options and it is based off of a whole different set of tables with the driver information, default pay types etc. Once I decide on how to handle carrier and customer, fleet should fall into place.

 

[CraigDolphin]

I could create 3 tables for each option

3 new tables? I thought I only suggested two to deal with storing estimate data, but I am reconsidering after your more detailed explanation re fleet and carriers.

Let me check further.

A load gets shipped to a customer. You have two potential methods of delivering it to the customer: Use a carrier, or use a driver in your own fleet.

The customer provides certain information that goes into determining the price you pay/charge. You need to store that information, and as there is only one customer per load, this could be stored in the orders table directly.

The delivery person, be they a carrier or a fleet person, may have different estimates of those same bits of information. You need to store that information too.

What if you try to get estimates from more than one carrier? You can only send the load via one carrier, but you might get information/estimates from more than one carrier (e.g., from a non-fleet carrier and from a fleet carrier, or maybe you get estimates from two competing non-fleet carrier firms....I dunno if this is remotely possible in your situation or if you even care about saving details of estimates from carriers who ultimately do not take the load itself).

So, assuming you do care, then maybe consider this:

tblOrders
-LoadID (pk)
-CustomerID (fk)
-CarrierID (fk) (this is the id of the carrier who actually takes the load)
-OrderTakenBy _Text (not sure if this is redundant with the previous field)
-CallFirst _Yes/No
-Hazmat _Yes/No
-Notes _Memo
-Customer_Estimated_Mileage (req'd)
-Customer_Estimated_Hours (req'd)


tblCustomers
CustomerID (Pk)
Customer_Name
CustomerAddress_Street
CustomerAddress_City
etc

tblCarriers
CarrierID (pk)
Fleet (Y/N) (here's where you assign whether a 'carrier' is in your fleet or not)
etc

tblCarrierDataEstimates (this contains one record for every carrier who provides estimates for a particular load)
CarrierDataEstimateID (auto, pk)
CarrierID (FK)
LoadID (FK)
Estimated_Mileage (req'd)
Estimated_Hours (req'd)
etc

The primary advantage to this scheme vs yours is that you can require fields in a table be filled out far more easily than you have ensure that a needed record in a related table is present (i.e., your IsMain = yes record). It can be done, don't get me wrong. It's just a lot more fiddly and requires vba to make sure everything has been added where you want.

As before, your main form is based on your orders table and includes fields for the customer's estimate of mileage etc.

One subform is based on tblCarrierShippingDataEstimates (however this time use multiple form view so you can view more than one record at a time).

You add as many records as necessary via the subform to capture all the potential carrier-derived estimates that you will ever encounter: be it only one, or two, or 30 for each load.

You can always look up whether an estimate was for a fleet carrier because each carrier is identified as fleet or not fleet in the Carrier table.

That said, your statement about pay for fleet carriers being driven off a whole different set of table than non-fleet carriers makes me a bit nervous. Just to be clear: if you need to track specific fleet drivers by load, then you will need a CarrierID for each fleet driver rather than just one CarrierId for your organisation's whole fleet. By comparison, if you're talking a non-fleet carrier and you don't need to track their specific drivers, then you can just have one CarrierID to represent that whole organisation/company.

So your carrier table might look something like:

CarrierID CarrierName Fleet
1 UPS N
2 Fedex N
3 Bob Blair Y
4 Chuck Norris Y
5 Willie Loman Y
etc

then you just need some tables to define pay rates etc vs CarrierID.

Now if you also need to track which fleet vehicle is used to move a load then there's a whole new wrinkle to factor into this. Hopefully you don't have to go there

Now, these are just some thoughts to kick around.

If you really will only ever store one carrier's data estimate per load, then I honestly don't see the problem with storing that as additional fields in the orders table.

Normalization requires that the data in the field be non-repeating and dependent on the primary key of the table.

In a one-carrier-estimate-per-load scenario, and a one-customer-estimate-per-load scenario, this information does depend solely on the loadID and you're storing similar, but fundamentally different, data in what, 4 extra fields in that one record? [customer_mileage,customer_hours,carrier_mileage,carrier_hours].

By comparison, even if we simplify your orderdetails table to have one sourcetype field instead of your three Y/N fields, you're still storing 5 extra fields [the orderdetails table's pk field, loadID fk field, source_type{main or carrier}, mileage, hours] in two records for every record in your orders table. That's 10 bits of extra information in your model vs 4 in the extra fields approach. As you can see, it's just more efficient to store it as extra fields in the one orders table if you don't have to consider a one-to-many situation. Not to mention, a lot easier to enforce that these fields are required. But if you do have to store more than one carrier estimate for a load, then the model I proposed above should work well for you.

Take what you find useful. Ignore the rest

 

[Glytch]

Thank you very much for your advice. I'm going to play around a bit with all of this tomorrow and I'll post back with what I've decided to go with. You have me convinced right now that that the extra fields in the order table is the way to go since the data for the customer/carrier will be unique to each load.

Right now I am storing the fleet information in a different table from the carriers because of all the extra information we keep on record for our drivers (SSN., license information, inspections, violations). I'm probably going to create a table for order details that involve a fleet since the payment options can vary so much (a driver may be getting paid a flat rate for hours but also another amount for empty miles traveled).

Anyway, thanks again and I'll post back tomorrow with an update.