On 3/26/07, James Wilford <james at jwilford.co.uk> wrote: > The first problem I am having is how to deal with returns, and how to > represent them when viewing past orders and compiling sales reports. I > can see several options: > > 1. Add a row to the sale_items table with a negative price to represent > a return. > 2. Mark the existing row as returned with an extra field. This poses > another problem - what to do if the quantity purchased was more than 1 > but not all are returned. > 3. Store returns information in a separate table. Any of these options are fine, depending on your requirements. For #2, you would need to use a returned quantity rather than a binary marker. Personally, I would use #1. This would allow for extra flexibility should you ever need to support "partial returns" (e.g., item X was returned, but the customer was only given a 90% refund due to restocking fee). You could do this in #3 as well, but I'm not sure I see the point of an extra table. However, you will want a status or transaction type field in solution #1 so that you can report on amount of returns (rather than just assuming that any negative value is a return). > A related problem is how to display sales in the admin side on a > per-product basis. In my schema, a sale is an order that has been > checked out and paid for (a basket is stored in the same way but > checked_out_at is null). But I need to display lots of information from > other tables in a sales report. I can either do this by joining a large > number of tables, or by having a separate sales table where all this > information is recorded when the order is paid. However, this does > represent some duplication of data, as the contents of the sales table > can be derived from the other tables. Do not cache statistics unless performance issues arise. Even then, there are options you can look at other than having a separate physical table that requires hooks in your code in all the right places so that the statistics are indeed correct. This design is a potential source of many bugs and should be avoided as a solution to performance problems unless it is clear that there *will be* performance problems. >From your description, I would definitely not expect performance problems. hth, -- Matt Warden Cleveland, OH, USA http://mattwarden.com This email proudly and graciously contributes to entropy.