Looking for ideas for DB schema/normalization (web store)

view full story

http://www.daniweb.com – 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 (General)