I have been wrestling with this for a few days and am looking for some recommendations on how to approach this (table(s) for store orders). This is for a web store application I am designing for myself. A while ago I downloaded osCommerce and dissected it. If I remember correctly it had 1 table for bill/ship address, postage,tax, user id, order id, cart id... and a few others. And another table called something like order_cart... which contained the items for an order.
That seemed relatively simple.... then my brain got in the way, looked at it and made things a bunch more complicated. Depending on where the owner of the store is depends on what kind of taxes need to be assessed.... locality tax, sales tax, VAT, etc. Then there is of course coupons, special offers and other discounts.... more than one may be applied per order. Perhaps I should break the order down into shipments and associate S&H per shipment and not per order? Then comes the matter of addresses... usually a customer will ship to the same address multiple times. Should these just be stored in a separate table?
This is the schema I came up with so far to work with Google Checkout. Would any improvements be in order? I guess there are a lot of ways to tackle this. I just wanted something that was robust in the end and can handle a variety of situations. Thanks for the help. (k) = key (p) = primary key (f) = foreign key (u) = unique
//Associates GC (or other 3rd party) id's to internal id's
LU_UID
_3rdPartyID (k)
uid
LU_OID
_3rdPartyID (k)
oid
REGIONS
rid (p)
parentid (k) //refers to a rid entry
name
USERS
uid (p)
email (u)
password
address
city
rgn_id //refers to rid in REGIONS
zip
ctry_id //refers to rid in REGIONS
ORDERS
oid (p)
uid (k)
state
total
currency
//bill/ship addresses to be added
ORDER_LIST
oid (k)
pid //product id
code //product code
qty
unitprice
So many of the features I had discussed aren't even in the database and based on what I've been reading over the past few days about data integrity should the tables be changed to something like:
LU_xxxx
_3rdPartyID
_3rdPartyName enum('Paypal','Google','AlertPay',...)
xid (f)
(_3rdPartyID,_3rdPartyName) (p)
REGIONS
//Unchanged but could contain cities, counties, postal codes in this implementation
//Before it was really intended just for provinces and countries.
USERS
uid (p)
email (u)
password
ORDERS
oid (p)
uid (f)
time
state
abid (f) //refers to aid in ADDRESSES
asid (f) //refers to aid in ADDRESSES
total
currency
ORDER_LIST //essentially unchanged... add foreign keys if necessary
ADDRESSES
aid (p)
hash (u) //hash of address values..... redundant with aid? would increase DB size if used as a foreign key?
address //All address lines identifying street/building/box concatonated with a special character
city //refers to rid in REGIONS
postcode //refers to rid in REGIONS
//Country and State can be determined by pid of city
/*Here is where why brain gets in the way..... What about cities with the same name in different states? The pid will be different but do I really need to make another table to normalize the DB properly? What happens if a country changes names? To maintain "proper" records shouldn't previous orders keep the previous name? Or worse a country splits? New tables for city/postcodes and a lookup table?*/
SHIPMENTS
oid (f)
time
tracking
carrier (f)
s&h
ORDER_ADJ
oid (f)
adjid (f)
amount
ADJ_DESC
adjid (f)
descript
LU_PRODADJ
adjid (f)
pid (f)
code (f)
ADJUSTMENTS
adjid (p)
code varchar
type //fixed or percentage
adj //i.e. 0.33% $10.00 etc
//The thinking here is ORDER_ADJ contains the specific currency adjustments to an order contained in the ORDERS table. ADJ_DESC provides desciptions for the adjustments.... NYC Locality Tax, NY Sales Tax, Grand Opening Sale, etc. Or should taxes be separate to properly normalize the DB? LU_PRODADJ exists to apply discounts to a particular product or more specifically a certain size/color/etc. Finally ADJUSTMENTS are just that.... the adjustments :)
Thanks for any help/advice