[thelist] database schema design for online store

[Max Schwanekamp]

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
NeptuneWebworks.com
541-255-2171