[thelist] database schema design for online store

James Wilford james at jwilford.co.uk
Mon Mar 26 15:31:01 CDT 2007


Hi,

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.

I've got an orders table, a consignments table, and a sale_items table. 
When the customer puts items into the basket they are stored as 
sale_items against consignments in the order. The consignment depends on 
which user will ship the items, as some items are shipped directly by 
the seller rather than by me. When the order is checked out it is 
timestamped with the date of checkout and the date of payment when the 
payment is cleared. The relevant part of the schema is like this:


CREATE TABLE orders (
    id serial NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    checked_out_at timestamp without time zone,
    paid_at timestamp without time zone,
    user_id integer,
    address_id integer,
    postage numeric(8,2) NOT NULL,
    total_price numeric(8,2) NOT NULL,
    payment_type character varying(10),
    anonymous boolean DEFAULT false,
    paypal_txn_id character varying(20)
);

CREATE TABLE consignments (
    id serial NOT NULL,
    order_id integer,
    shipping_user_id integer,
    shipped_at timestamp without time zone,
    postage real NOT NULL
);

CREATE TABLE sale_items (
    id serial NOT NULL,
    product_id integer NOT NULL,
    consignment_id integer NOT NULL,
    created_at timestamp without time zone,
    quantity integer DEFAULT 0 NOT NULL,
    unit_price numeric(8,2) NOT NULL,
    commission numeric
);

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.

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.

I hope this is enough information to get an idea of what I'm puzzling 
over. I'd really appreciate some insights from people out there who've 
been through these kinds of decisions before, and how you did things.

Cheers,

James

Brighton, UK




More information about the thelist mailing list