this is all about normalization, there's a relationship between the user, the order and what was bought in the order. You'll have a one to many relationship between users and orders(a user can have multiple orders but an order can't have multiple users) and a many to many relationship between products and orders (an order can have multiple products and products can be in multiple orders)
On Tue, Feb 8, 2011 at 9:34 AM, Dave Burns <[email protected]> wrote: > > I've inherited a site at a customer and the previous (several) people working > on this code did them no favors. For example, it's an e-commerce site and the > table that records sales keeps track of the user id and the amount paid but > not what product was bought or the form of payment. There actually IS a > record of what the user bought but it's stored in their user record which is > overwritten with each purchase so any history is lost. In addition, the site > has discount-codes (coupons) but those are stored with the user, not the > transaction so history is lost there too. In addition, I've noticed that > discount codes are re-usable and can be edited without saving history so if > you change the discount amount for future sales, running a historical report > for sales involving that discount code are now bogus. > > I've been asked to fix this. I wonder if there is a standard pattern for the > schema here since it must have been done a million times by now. I have never > used an off-the-shelf shopping cart package either - I have always assumed > that those are more about maintaining sessions/carts and interacting with > payment gateways but it occurs to me that maybe they include the schema > design I'm thinking of that allows proper accounting audit trails, etc. > > Any advice? > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341976 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
