Basically I've got two tables: Customers and Orders.

Now, there are two options and I'm not sure which is the best route.

Option 1) Save "Total Orders Value" and "Number of Orders" as fields in the Customer record (that holds no other order info except for name and address etc) and then when filling a listbox (to display results) from a query to the Customers table, I can just put these values in the relevant cell.

This however means that every time a new order is added to an existing customer's record I have to update the record by adding another order to "Number of Orders" and the new order value to "Total Orders Value". It also duplicates values from the Orders table but this route may still prove more efficient with thousands of records.

Option 2) Don't save these two fields to the Customer's record, and instead when filling a listbox from a query to the Customers table, just run another query within the recordset do loop query that grabs the relevant data from the associated orders records.

Now this is simply done, ie. SELECT Count (*), SUM (OrderValue) from Orders where CustomerID = ID" etc.

BUT.. would this method slow the entire process down if there was a lot of data to search through due to there being one query loop within another query loop that effectively means sifting through all the records twice?

I really can't decide which is more efficient.

Any advice?

Here's a suggestion: make it so that you can change your mind at a later date without a major code re-write, by creating a view like this:

CREATE CUSTOMER_VIEW AS
SELECT C.CustomerID, C.CustomerName, C.CustomerAddress, Count (O.OrderID) as OrderCount, SUM (O.OrderValue) as TotOrderValue
FROM Customers C
LEFT OUTER JOIN Orders O ON O.CustomerID = C.CustomerID
GROUP BY C.CustomerID;

Now use that view in your application. If testing indicates that the performance isn't good enough and you decide you really need to store those values in the Customer table, then you can do so and then change the view definition to:

CREATE CUSTOMER_VIEW AS
SELECT C.CustomerID, C.CustomerName, C.CustomerAddress, C.OrderCount, C.TotOrderValue
FROM Customers C;

I would go for the version without the stored summary columns first, because it involves less development and there is less likelihood of errors.

Hi,

Thanks for the reply.

The thing is, I'm creaitng the initial empty (default) database via my developer's IDE (hard-coding it) as opposed to creating it through code when the program opens. Because of this, I need to make the right choices now and unfortunately don't have thousands of records to test speed and performance via both routes and the way you suggest.

I was hoping there was a simply rule of thumb for something like this so when a listbox is filled with the details of every customer in the database row by row I know the optimal way to perform this data retrieval and display.

On one hand it feels like adding the summary columns in the customer record is quicker for the full customer list display but is it "bad programming" to do this?

Is the more professional way to just get these values by querying the Orders table while in the main query loop of the Customers table?

Also, (without wanting to confuse you), if you have thousands of customer records, does it make more sense to display 100 at a time and give the user the option of moving back and forth 100, rather than filling a listbox with everything all at once? Something tells me this is not really practical and could really slow an application down.

Thanks.

Denise

Hi,

Thanks for the reply.

The thing is, I'm creaitng the initial empty (default) database via my developer's IDE (hard-coding it) as opposed to creating it through code when the program opens. Because of this, I need to make the right choices now and unfortunately don't have thousands of records to test speed and performance via both routes and the way you suggest.

I was hoping there was a simply rule of thumb for something like this so when a listbox is filled with the details of every customer in the database row by row I know the optimal way to perform this data retrieval and display.

On one hand it feels like adding the summary columns in the customer record is quicker for the full customer list display but is it "bad programming" to do this?

Is the more professional way to just get these values by querying the Orders table while in the main query loop of the Customers table?

Also, (without wanting to confuse you), if you have thousands of customer records, does it make more sense to display 100 at a time and give the user the option of moving back and forth 100, rather than filling a listbox with everything all at once? Something tells me this is not really practical and could really slow an application down.

Thanks.

Denise

My rule of thumb would be not to store counts and totals that you can get at run time.

Regarding the thousands of customers: yes, just displaying a fixed number at a time (perhaps 10 or 20 rather than 100) is preferable to loading them all at once. Look at any well-known web site for examples of such principles: Amazon, Google, etc. They all show you the first N matches, and then offer you Next/Previous functionality.

Thanks Tony. I'll give it all some thought.

Think about this: if you have thousands of customers, then you will have the issue of updating the customer record each time you enter an order. Now, if the bussiness only enters customers when order are being taken and you have only one order per customer, it could be a wash in terms of how often each lookup / calculation needs to be done. But, if you have multiple orders per customer, then you wind up having to do many more lookups with the numbers in the customer record.

Also, think about this: when doing customer queries, if it's important to know how many orders they've placed and the total value, it's a good bet that they are going to also want (in many cases, anyway) to see the customer history, in which case, that lookup is going to have to be done anyway.

Good points. Thanks.