[thelist] database schema for shopping transaction

[Chris W. Parker]

Anthony Ettinger <mailto:apwebdesign at yahoo.com>
    on Thursday, November 03, 2005 1:18 PM said:

> Anyone have any good pointers on this?
> 
> The idea is to have an online shopping cart where the
> customer can place multiple items (and quantities).

(A) Although some people might disagree with this... I've found it VERY
useful in my environment to identify products through an autonumber id
rather than through their individual sku (of course the sku still needs
to be a unique field). This will allow you to change the sku at a later
date (as I've had to do) without needing to update a huge number of rows
to reflect the new sku.

products (id [PK], sku, name)

1	Product001	Feet Stink Perfume
2	Product002	Your Mother's Used Bar of Soap

users (id [PK], name)

1	Chris
2	Anthony

carts (id [PK], user_id [fk])

1	1
2	2

carts_contents (cart_id [fk], product_id [fk], qty)

1	1	10
1	2	3
2	2	14


Now conversely if you used the sku to uniquely identify the product
throughout the db you'd have this:

products (sku [PK], name)

Product001	Feet Stink Perfume
Product002	Your Mother's Used Bar of Soap

users (id [PK], name)

1	Chris
2	Anthony

carts (id [PK], user_id [fk])

1	1
2	2

carts_contents (cart_id [fk], product_sku [fk], qty)

1	Product001	10
1	Product002	3
2	Product002	14

But what if you wanted to change Product002 to Product002a? You'd have
to go through every table and change each occurence of Product002 to
Product002a.

(B) Do you need to know when the last time a product was updated? What
about the price specifically? Should that be on a different timer? (In
my case, yes it should be.)

(C) Do you plan to have different staff members with different levels of
security?

(D) Do you want to know who created what product as well as have a
"paper" trail of who edited what and when?

(E) Will you have multiple pricing levels for different customers?

etc.


There are a ton more things to consider. I suggest you sit down and
really think hard about what you want your cart to be capable of and go
from there. Most of my good ideas were only discovered once I said to
myself, "Gosh I should have built x into the app." and "Wouldn't it be
useful if we could know/do y!"


Good luck.
Chris.