[thelist] database schema design for online store

Max Schwanekamp lists at neptunewebworks.com
Mon Mar 26 16:58:05 CDT 2007

James Wilford wrote:
> I'm developing an online store site using Ruby on Rails and I'm looking 
> for some advice on database design, and best practices as I haven't done 
> this type of site before.
> 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.

I'd go with #3.  I've found that it's helpful to differentiate between 
purchase payments, refunds, fees and other miscellaneous transactions, 
plus I'd tend to decouple your orders record from a particular payment 
processor (i.e. your orders.paypal_txn_id column).  So I'd maintain 
separate tables for orders and transactions, with a one:many 
relationship.  Incomplete orders would have no transaction, completed 
orders would have at least one transaction (the payment), and perhaps > 
1 transaction, e.g. refunds (sometimes more than one partial refund) or 
additional fees.  Not sure what rdbms you're using, but given your 
orders table ddl, the transactions table would be something like:
CREATE TABLE transactions (
id serial NOT NULL,
order_id integer NOT NULL,
created_at timestamp,
amount numeric(8,2),
processor varchar(20),/* e.g. paypal|worldpay|check|cash */
processor_txn_id varchar(20),
description smalltext

Max Schwanekamp

More information about the thelist mailing list