[thelist] database schema design for online store

[Matt Warden]

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.