[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
NeptuneWebworks.com
541-255-2171
More information about the thelist
mailing list