I am working on the next generation of ecommerce et. al.
I have had a retail store for close to a year now and have seen many different schemes. Internally I use MS RMS, although I would love to change as the database stuff really sucks from a design standpoint. I use it because it works, period. It isn't the best, but I need a GUI for my employees and the Linux/Unix options suck at best. Also, Quickbooks integration is essential, as I don't like paying CPA's to reenter my data.
So, now that motivation is out of the way, I still use OACS for ALL my sites. With 5.2 ecommerce is marginally working but definitly showing its age. I am proposing the following (something like dotfolio or dotwrk), a decoupled ecommerce using the best of OACS and XOtcl extensions.
I have a new package called shopping cart which only deals with,,,, shopping carts. I also have the start of a simplified and extensible products db. I need to flesh out categories, relations to other products etc.
I am combining experience with OACS and a retail store, OSCommerce with all it's worts many are using it, the old ecommerce from OACS and aspects of MS RMS.
I need a package for import/export of data ( I volunteer myself and the reality is it should have been started 2 years ago when I talked at length with Talli and Roberto at LinuxWorld SF).
Here is my proposed (and very basic) intro to the cart/products database. Keep in mind that extensibility is important (i.e. events can also be in the cart, although I feel bad as one of the original authors at AD of the events/activities extreme normalization and overengineering, but hell, it does more than most event packages ;) )
------------
// Unlike the current ecomm,
// A customer can have more than one cart active.
// The cart should be able to be
shipping gateway stuff {
via_id // what company
}
shippers {
shipper_id
lots of shipper info if you really need it
}
shopping_cart {
cart_id
product_id
user_id ? // maybe an non-registered user can have a cart
quantity
date_added // scheduled proc to clean
comments // for specials etc.
}
shipping {
cart_id
shipping_amount
shipper_id
}
// the actual invoice
// this may not happen in real time.
settlement_header {
customer_id
bill_to // this needs to use the ref stuff
ship_to // ""
card_id // if we store card, don't know if that is totally legal with Visa/MC
status_id // FK to a status table
shipped_p // something that links to
}
settlement_detail {
cart_id
product_id
price_ea
invoice_price_ea
tax_p
tax_amount
quantity
}
// how to deal with ....
// buy this get that for $$
// buy one get one for xxx
// etc.
promo_matrix {
promo_id
desc
}
// not really for the cart.
products {
product_id
sku // internal store number
image
weight // needs conversion, i.e. us/metric
}
// localized info
products_description {
product_id
language_id // or whatever the oacs equiv is
name
description // could be blob
views // maybe track how many views by locale
}