What is the ideal shopping cart schema?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

What is the ideal shopping cart schema?

Post by allspiritseve »

I've been discussing this on sitepoint and gotten some interesting feedback, so I'd like to pose a similar question here: What is the ideal shopping cart schema? To start with, I'd like to have multiple attributes for a given product, and multiple values for those attributes. (Some might consider a given combination of those attribute values to be a product-- ie a Large Red Shirt vs. a Shirt -- which is the product?) Magento uses an EAV schema to handle attributes. I would like to stay away from an EAV schema if possible.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: What is the ideal shopping cart schema?

Post by Christopher »

allspiritseve wrote:Magento uses an EAV schema to handle attributes. I would like to stay away from an EAV schema if possible.
Before you throw away an EAV scheme, remember what EAV is good for. EAV is a solution to the problem that you have a wide variation in the number of attributes that an entity can have. That is a pretty good description of product attributes.

I have found that a SKU based design works the best because each SKU is a unique set of attributes. So when you load all the SKU records for a 'product' you have all the attribute data. As I think I mentioned before, I consider the 'product' in this sense to be the bottom layer of the product category navigation system/data. So I have one system and tables for the navigation side of products, and a table of SKUs and related data on the purchasable side (possibly an EAV). That allows the cart to stay focused on SKUs and away from the product presentation data.
(#10850)
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: What is the ideal shopping cart schema?

Post by allspiritseve »

arborint wrote:Before you throw away an EAV scheme, remember what EAV is good for. EAV is a solution to the problem that you have a wide variation in the number of attributes that an entity can have. That is a pretty good description of product attributes.
What about searches? That's a pretty big part of e-commerce. EAV is not optimized for searching.
arborint wrote:I have one system and tables for the navigation side of products, and a table of SKUs and related data on the purchasable side
Could I see some of this? That's the schema stuff I'd like to be discussing.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: What is the ideal shopping cart schema?

Post by Christopher »

allspiritseve wrote:What about searches? That's a pretty big part of e-commerce. EAV is not optimized for searching.
I really don't think there is going to be much of a performance problem searching an EAV table that only contains some product attributes. I am not talking about storing SKUs or the presentation data in an EAV -- just attributes.
allspiritseve wrote:Could I see some of this? That's the schema stuff I'd like to be discussing.
Typically it is just a product table with SKUs and then one or more tables for the presentation/orgainization side. It can be a single category table, or multiple tables if you want products to be in multiple categories. There are other things like putting users in buying groups that have subsets of SKUs or special pricing/discounts.
(#10850)
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: What is the ideal shopping cart schema?

Post by allspiritseve »

arborint wrote:Typically it is just a product table with SKUs and then one or more tables for the presentation/orgainization side. It can be a single category table, or multiple tables if you want products to be in multiple categories. There are other things like putting users in buying groups that have subsets of SKUs or special pricing/discounts.
How do you deal with the user's concept of a product, which encapsulates multiple SKUs? Do you duplicate data in shared columns (like a product name and description) or separate those columns into a parent table? How do you deal with product 'types' such as a wine bottle where every wine bottle comes in one of three volumes? Some of this may be more of an application design question than a database schema question, but it's worth posing in case you do have ways of dealing with these issues with the db.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: What is the ideal shopping cart schema?

Post by Christopher »

allspiritseve wrote:How do you deal with the user's concept of a product, which encapsulates multiple SKUs?
What I usually what I do is essentially two tables -- the 'category' table contain the hierarchy of what the user considers categories and products. I consider the end of any branch in the tree to be a 'master product' or 'base product' (call it what you like). Then I have a 'product' table with all the SKUs. Each SKU has a relation back the the 'base product' in the category table so the display system can find the associated SKUs.
allspiritseve wrote:Do you duplicate data in shared columns (like a product name and description) or separate those columns into a parent table?
I don't usually duplicate data, but sometimes I might have titles the user does not see so that something reasonable shows up in the Admin interface when dealing with SKUs. I would push columns common to all SKUs into the category table. But each SKU can have a individual title which might be a full name or just 'blue' or '750ml'.
allspiritseve wrote:How do you deal with product 'types' such as a wine bottle where every wine bottle comes in one of three volumes?
I put that information into the SKUs usually, each SKU would have a 'size' field that contained the volume for that SKU.
allspiritseve wrote:Some of this may be more of an application design question than a database schema question, but it's worth posing in case you do have ways of dealing with these issues with the db.
Yes it it.
(#10850)
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: What is the ideal shopping cart schema?

Post by allspiritseve »

arborint wrote:What I usually what I do is essentially two tables -- the 'category' table contain the hierarchy of what the user considers categories and products. I consider the end of any branch in the tree to be a 'master product' or 'base product' (call it what you like). Then I have a 'product' table with all the SKUs. Each SKU has a relation back the the 'base product' in the category table so the display system can find the associated SKUs.
Ok, so:

Code: Select all

categories
------------
id parent_id name
1 null          wine
2 1             l.mawby (maker)
3 2             Blanc de Blanc
 
products
---------
id category_id size
1 3                375ml
2 3                750ml
3 3                1.5L
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: What is the ideal shopping cart schema?

Post by Christopher »

Yes, exactly. In 'category' I usually have a field to indicate whether it is a 'base product' or a 'category'. And in the 'product' table there are lots of columns for price, weight, etc. Both might have descriptions, images, etc.
(#10850)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: What is the ideal shopping cart schema?

Post by VladSun »

Just a warning - some of the products may be composite products. Keep that in mind while designing your DB.

We had huge problems implementing such a schema for Pizza Hut, especially promotions, and some problems with menus.
Some of them:
- a menu may contain a "choose-from-set" product in it.
- promotions of type "pay one get two" - sounds simple but when it is in fact:
"pay one and get second of the same type with the next smaller size, or second of different type with a lower price (size doesn't matter in this case)"
or
"pay one and get second of the same size, but only if attributes are the same or less"

etc.
Last edited by VladSun on Fri Jun 26, 2009 1:50 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: What is the ideal shopping cart schema?

Post by matthijs »

With Arborints schema, as I understand it, you still have the problem of how to solve the variable amount of attributes. For a whine bottle you have size. But for a different kind of product it will be weight. Another has color, material, etc etc

Or do you store those attributes in a separate, EAV-like, table, like:

Code: Select all

 
table Attributes
id
attr_name
attr_value
 
[edit]
and then use a lookup table product2attribute to link them together?

(by the way allspiriteve, I'm going to run some tests similar to wardrops tests to see when the EAV schema breaks down, but that has to wait till tomorrow)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: What is the ideal shopping cart schema?

Post by Christopher »

I strongly second Vlad's comments. Things like the ones mentioned can be a nightmare to code. I have found on the category/product side that often you just have to write custom code to deal with these things. They do not fit within your standard category/product presentation scheme. So allow for that.

On the checkout side (which has not been discussed ;)) I find that having a custom Checkout object that know about these special conditions helps a lot. It is the Model that connects the cart, the user data and these special conditions together through the checkout process.
(#10850)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: What is the ideal shopping cart schema?

Post by VladSun »

I think it's the final (well part of it) DB design:
Attachments
products-db.png
products-db.png (38.88 KiB) Viewed 4833 times
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: What is the ideal shopping cart schema?

Post by onion2k »

For the most recent store app I wrote I used a combination of SKU and EAV. There's a record for each product with a one-to-many relationship to each SKU within that product group (eg a 'fishing rod' product, then SKUs for each size within that product group). Then there's a separate set of tables for attributes with a one-to-one relationship to the products and the SKUs.. eg each product has a set of attributes (length, colour, weight), then each SKU has a value for each product attribute. It's complicated but as the store has a relatively small number of products but a large number of SKU variations for each product it was necessary.

It gets worse too. Every product has multiple descriptions for different languages, images that are only displayed in certain regions, and every SKU has prices for different currencies (and different regions now... there are different Euro prices depending on which bit of the Eurozone you're browsing from). Product display pages end up joining about half a dozen tables together just to pull out the SKU data. :)

I handled special offers like "buy 2 get 1 free" by limiting it to SKUs within the same product group and always giving the cheapest one free. The whole offers system is completely separate to the SKUs and Products though, so extending it for more complicated product offers in the future is definitely possible.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: What is the ideal shopping cart schema?

Post by allspiritseve »

How do you guys handle order data after the order has been completed? For example, a user may have ordered a certain product that was deleted after the order was placed. It seems like most or all of the product data should be saved somehow, I don't know if it's better to keep that in a separate table, or just put a flag on old products to mark them as deleted?

Do you treat a cart differently from an order? I currently have separate tables: carts & cart_items, orders and order_items. Maybe they should be combined?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: What is the ideal shopping cart schema?

Post by Christopher »

I use separate cart and order tables just like you do. I don't delete products, just mark them as unavailable. You do have to save the price and any other volatile information in the order item record because they will change in the product records.
(#10850)
Post Reply