CD Baby rewrite : a shipments table!

by Derek Sivers

In 1.2 million CDs sold, I had never thought of "shipments" as a separate table. I always thought of it as attributes of an order, but if we're going to be sending things from multiple locations, we'll have multiple shipments for an order, each shipment with its own attributes, so...

SITUATIONS:
* - some items in an order backordered, so they're shipped later
* - FedEx as main shipping method, but one backordered CD sent USPS later
* - one CD sent from Japan, one CD sent from Canada, both to a person in Switzerland
* - customer orders only one "merch" item, which the musician sends themselves: "shipment" is what that merchant tells us it is
* - some items in an order sent from our warehouse, some from self-ship merchant

new idea: make a table to keep track of shipments. a shipment has its attributes that need to be kept track of, and the lineitems in an order just link to this shipment.

This kinda turns our existing internal model upside-down, but the more I think about it, I realize it makes a lot of sense. I love it when you come across things like this that make you look at your system in a whole new way.


CREATE TABLE shipments (
id serial PRIMARY KEY,
warehouse_id int REFERENCES warehouses(id) ON DELETE RESTRICT,
address_id int not null REFERENCES addresses(id) ON DELETE RESTRICT,
date_shipped timestamp(0) with time zone,
shipped_by varchar(8),
ship_method_id int REFERENCES ship_methods(id) ON DELETE RESTRICT,
tracking text
);


CREATE TABLE lineitems (
id serial PRIMARY KEY,
inv_id int not null REFERENCES invoices(id),
item_id int not null REFERENCES items(id),
address_id int REFERENCES addresses(id),
shipment_id int REFERENCES shipments(id),
linestatus int not null REFERENCES line_status(id) default '1',
quantity int not null default '1',
currency char(3) not null default 'USD',
price numeric(7,2) not null,
wholesale numeric(7,2) not null,
shipcost numeric(6,2),
soundscanned date
);


Nome sane?


1 Comments

billgood
2005-01-28 03:50:50
Also, you can ship to multiple locations
In our case, we moved shipment info to another table to allow shoppers to ship goods to multiple addresses. For instance, they may buy an item for themselves and a gift for someone else (all on the same order). We also found that it allowed the system to charge customers only as their products were shipped.


Of course, allowing multiple destinations also mean a change to the way we handle sales tax, which is calculated on destination, but that's another story.