What is the ideal shopping cart schema?
Moderator: General Moderators
- 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?
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.
- 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?
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.allspiritseve wrote:Magento uses an EAV schema to handle attributes. I would like to stay away from an EAV schema if possible.
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)
- 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?
What about searches? That's a pretty big part of e-commerce. EAV is not optimized for searching.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.
Could I see some of this? That's the schema stuff I'd like to be discussing.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
- 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?
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:What about searches? That's a pretty big part of e-commerce. EAV is not optimized for searching.
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.allspiritseve wrote:Could I see some of this? That's the schema stuff I'd like to be discussing.
(#10850)
- 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?
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.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.
- 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?
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:How do you deal with the user's concept of a product, which encapsulates multiple SKUs?
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:Do you duplicate data in shared columns (like a product name and description) or separate those columns into a parent table?
I put that information into the SKUs usually, each SKU would have a 'size' field that contained the volume for that SKU.allspiritseve wrote:How do you deal with product 'types' such as a wine bottle where every wine bottle comes in one of three volumes?
Yes it it.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.
(#10850)
- 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?
Ok, so: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.
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- 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?
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)
Re: What is the ideal shopping cart schema?
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.
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
Re: What is the ideal shopping cart schema?
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:
[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)
Or do you store those attributes in a separate, EAV-like, table, like:
Code: Select all
table Attributes
id
attr_name
attr_value
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)
- 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?
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.
On the checkout side (which has not been discussed
(#10850)
Re: What is the ideal shopping cart schema?
I think it's the final (well part of it) DB design:
- Attachments
-
- products-db.png (38.88 KiB) Viewed 4835 times
There are 10 types of people in this world, those who understand binary and those who don't
Re: What is the ideal shopping cart schema?
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.
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.
- 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?
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?
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?
- 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?
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)