Customer Order Database

[MattBaldry]

Hello all. I am after some advice on a database I have built but thinking it may not be the most efficient way. At the moment I have the below tables.

tblCustomer
tblCustomerContact
tblCustomerDeliveryAddress
tblSalesOrder
tblSalesOrderItem

So I add the customer. The customer may have more than one delivery address so I add them in the relevant table via an entry form.

Now, when this customer places an order, at the moment I link the delivery address via the DeliveryAddressID to the order.

My thoughts on this are, this would only show the current data for the ID and not the data that was in place when the order was raised. Someone may edit the data by mistake so the order would in future show the edited, incorrect data and not the address as it was when the order was raised.

Would it be more efficient to use a drop down to select the delivery address, but then insert this data into the tblSalesOrder with fields named the same, or is this bad practice?

~Matt

 

[theDBguy]

Hi Matt. If the possibility of that exists and you don't want to take chances, then I think you don't have any choice but to store the duplicate information. Just one person's humble opinion...

 

[MattBaldry]

Hi Matt. If the possibility of that exists and you don't want to take chances, then I think you don't have any choice but to store the duplicate information. Just one person's humble opinion...

That was my thinking. No matter how much you cover for human error, someone can always find a way to error around it.

~Matt

 

[theDBguy]

That was my thinking. No matter how much you cover for human error, someone can always find a way to error around it.

~Matt

Hi Matt. Not sure what you mean by that. You can use code to "copy" the current customer data into your duplicate fields. If the information was a mistake to begin with, what you did still accomplish your original goal, which was to "store the original information" at the time of the transaction. Now, if you also want to update them, after a mistake, then that's another issue to consider. Only you can answer that question, since it is your business rules.

 

[vba_php]

Hello all. I am after some advice on a database I have built but thinking it may not be the most efficient way. At the moment I have the below tables.

tblCustomer
tblCustomerContact
tblCustomerDeliveryAddress
tblSalesOrder
tblSalesOrderItem

So I add the customer. The customer may have more than one delivery address so I add them in the relevant table via an entry form.

Now, when this customer places an order, at the moment I link the delivery address via the DeliveryAddressID to the order.

My thoughts on this are, this would only show the current data for the ID and not the data that was in place when the order was raised. Someone may edit the data by mistake so the order would in future show the edited, incorrect data and not the address as it was when the order was raised.

Would it be more efficient to use a drop down to select the delivery address, but then insert this data into the tblSalesOrder with fields named the same, or is this bad practice?

~Matt

Matt,

your table setup is fine. I would disagree with guy about storing duplicate data, because of the fact that you can prevent tampering with data that is entered initially by simply using locking and enabling properties on the form. or, there are other methods too...such as storing data for order entry in a separate table and then storing records for sales orders that are edited. although that process is prolly not used too often, it's one of many options available to you. but the bottom line is that, if you've just got a single bound form where youi're making the original sales data available to a user in which the user can edit the record at any time, that is certainly bad practice, because most businesses like to audit contract changes / sales order changes, who made them, when they made them, and the reasoning behind the changes. this forum has a FAQ on creating an audit trail as well, and I also wrote an article on it on an external website many years ago:

http://www.databasedev.co.uk/audit_trail.html

 

[plog]

Someone may edit the data by mistake so the order would in future show the edited, incorrect data

Isn't that true of all data, everywhere, all the time? I don't see how violating normalization will prevent mistakes. And who's to say this new method doesn't get mistakes added to it as well?

If you want a history of delivery locations or an audit trail of edits you can build those tables to do that. I don't think copying data hither and yon is the answer.

 

[Solo712]

Hello all. I am after some advice on a database I have built but thinking it may not be the most efficient way. At the moment I have the below tables.

tblCustomer
tblCustomerContact
tblCustomerDeliveryAddress
tblSalesOrder
tblSalesOrderItem

So I add the customer. The customer may have more than one delivery address so I add them in the relevant table via an entry form.

Now, when this customer places an order, at the moment I link the delivery address via the DeliveryAddressID to the order.

My thoughts on this are, this would only show the current data for the ID and not the data that was in place when the order was raised. Someone may edit the data by mistake so the order would in future show the edited, incorrect data and not the address as it was when the order was raised.

Would it be more efficient to use a drop down to select the delivery address, but then insert this data into the tblSalesOrder with fields named the same, or is this bad practice?

~Matt

Customers with multiple, or changing, delivery addresses are an extremely common occurrence. Even if in your case you have only one such customer at the moment, it makes sense to have a separate delivery address table in one-to-many relationship to the customer. This will make for a much more reliable audit trail.

Best,
Jiri

 

[Pat Hartman]

theDBguy is correct. If you have to be able to correctly identify the delivery address for old orders, then you MUST copy the delivery address into the order record. Just using a FK won't guarantee that the address didn't change over time.

 

[gemma-the-husky]

it really depends how important the old information was.

If a company changes a phone number, do you really need to store the old phone number. Ditto with an address.

However, if a client has multiple delivery addresses, then store this in an indexed sub table for the customer, with a flag to indicate "old address" - so the old address is available for existing orders, but not visible for new ones.