SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 96
-
Jun 16, 2009, 20:18 #1
- Join Date
- Dec 2002
- Location
- Ann Arbor, MI (USA)
- Posts
- 648
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Modeling product attributes/options
I'm not sure what the actual term is for these, but my boss calls them attributes and I've heard them called options: colors, sizes, etc. that may have varying prices. I currently have a table for products and a table for product attributes, but I'm running into a lot of problems that could be solved by refactoring my passed arrays into objects. (As I did the content pages first, products seemed easy enough using Gateways/arrays, but then when doing the shopping cart, I switched to mappers/objects which made my life a whole lot easier. Now that I'm doing more complex things with products, those arrays are getting mighty troublesome).
I guess I'm wondering how people would model products and their attributes. Currently I only need one attribute per product (typically size) and each attribute has a different price (most of what they sell is wine). Should I consider each attribute (ie, 750ml 'Blanc de Blanc') a "Product", and then each actual Product (ie, 'Blanc de Blanc') moved to some sort of product 'type'? I currently have prices in the attributes table. Also, when I am grabbing information for my cart I am grabbing all product_attribute values, but only the product name. That leads me to think the attribute is really the product, but I am unsure.
Edit: 1. I noticed Magento uses some EAV tables for their attributes. Is this common? I suppose its one of those situations where something like that becomes useful. 2. I have been thinking, and it seems like attributes should be fixed characteristics whereas options are what I'm looking for: things the user has to specify in order to place an order (such as bottle size). Sometimes that involves a changing price (375ml vs. 750ml wine bottle) and sometimes the price is fixed (S vs L shirt). That makes the most sense to me, though I still am not sure how they should be stored.Last edited by allspiritseve; Jun 16, 2009 at 22:20. Reason: Added some thoughts.
-
Jun 17, 2009, 00:36 #2
- Join Date
- Mar 2007
- Location
- Czech Republic
- Posts
- 375
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Regarding to EAV, I have recently browsed various discussions and all people (http://www.dbforums.com/ and elsewhere) say that EAV is evil. Good in beginning but unscalable. IMHO is EAV acceptable e.g. for history tables where only some columns change and these tables are rarely used.
Maybe - maybe!! - you could implement EAV tables, but only for search purposes
Code:select cols from main_table where id IN (select id from EAVattrs where attr_name = ...))
-
Jun 17, 2009, 07:40 #3
- Join Date
- Dec 2002
- Location
- Ann Arbor, MI (USA)
- Posts
- 648
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jun 17, 2009, 08:19 #4
- Join Date
- Mar 2007
- Location
- Czech Republic
- Posts
- 375
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
By reason that Magento's developers do not know best practices? Google returns many hits for combination "magento+slow"
Seriously - for low count of attributes this is feasible.
-
Jun 17, 2009, 09:07 #5
- Join Date
- Dec 2002
- Location
- Ann Arbor, MI (USA)
- Posts
- 648
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Ok, so what would be the 'best practice' way of storing attributes? Would you recommend a table for each product 'type', so a new attribute = a new column on that table?
Or would you prefer a one -> many -> many relationship, where one product has many attributes, and each attribute has many values? (Again, I'd probably call these options if the user has to choose one). This scenario seems very similar to EAV, just split into two tables.
-
Jun 17, 2009, 09:19 #6
- Join Date
- Jul 2006
- Location
- Augusta, Georgia, United States
- Posts
- 4,194
- Mentioned
- 17 Post(s)
- Tagged
- 5 Thread(s)
I would say that attributes represent a different product. A half gallon of milk is a different product than a gallon of milk. However, in order to relate the items I would use a abstract products table. I think a typical inheritance based table relationship would suffice.
abstract_products
-- id
-- name
-- description
products
-- id
-- abstract_product_id
-- name
-- description
-- price
In the previous scenario a half gallon and gallon of milk would both be "extending" abstract product milk. Yet, both the half and gallon of milk would be separate products.
Using a many to many relationship you could even implement a type of multiple "class" inheritance if needed although that doesn't seem the case.
Either way hypothetically the below would be represented in a table context.
PHP Code:abstract class Milk {
protected $name = 'Milk';
protected $description = 'Generic Description';
}
class HalfGallonOMilk extends Milk {
protected $name = 'Half Gallon of Milk';
protected $description = 'Override default description with this one';
protected $price = 1;
}
class GallonOMilk extends Milk {
protected $name = 'Gallon of Milk';
protected $price = 2;
}
The only code I hate more than my own is everyone else's.
-
Jun 17, 2009, 09:28 #7
- Join Date
- Dec 2002
- Location
- Ann Arbor, MI (USA)
- Posts
- 648
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
OK, so you favor what I've been calling 'Products' a product type. What about a large shirt vs a small shirt? Or a red can opener vs. a blue can opener? Are those all different products? How would you store all of those attributes (color, size, etc. assuming each product can have multiple values for each attribute and (potentially) multiple attributes for each product (though I only need one for this product).
-
Jun 17, 2009, 09:40 #8
- Join Date
- Jul 2006
- Location
- Augusta, Georgia, United States
- Posts
- 4,194
- Mentioned
- 17 Post(s)
- Tagged
- 5 Thread(s)
Originally Posted by allspiritseve
Originally Posted by allspiritsev
Originally Posted by allspiritsev
The only code I hate more than my own is everyone else's.
-
Jun 17, 2009, 13:10 #9
- Join Date
- Oct 2006
- Location
- France, deep rural.
- Posts
- 6,869
- Mentioned
- 17 Post(s)
- Tagged
- 1 Thread(s)
How about:
A table row containing a "large red shirt" already contains its own unique key.
items
====
type : shirt
color : red
size : shirt
Just make all fields 3 unique, use it for the wine, use it for the can opener, use it for milk and chocolate milk. Could it work?
-
Jun 17, 2009, 13:22 #10
- Join Date
- Dec 2002
- Location
- Ann Arbor, MI (USA)
- Posts
- 648
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
type : milk
color : chocolate
size: gallon
I suppose? Maybe?
I could do that for a limited number of products. But after a certain point, the table will have a ridiculous amount of columns and most of them won't be used in any given row. Thus some normalization is in order.
-
Jun 18, 2009, 10:41 #11
- Join Date
- Oct 2006
- Location
- France, deep rural.
- Posts
- 6,869
- Mentioned
- 17 Post(s)
- Tagged
- 1 Thread(s)
Actually I meant "Chocolate Milk" as being a separate product from milk, not a color attribute. Milk doesn't need a color does it?
I could do that for a limited number of products. But after a certain point, the table will have a ridiculous amount of columns and most of them won't be used in any given row.
Still musing then ... when I went back to your original post, I then thought that "color" is too specific, and is leading me the wrong way - how about "attribute"?
items
====
type : polo shirt
attribute : red
size : large
From the public perspective, that'd make nice, natural urls - which would be very easy to rewrite.
shop .com/polo-shirt/red/large
Be very easy to filter down to a select statement too, no de-referencing of id numbers.
Ah, but what if a product has more than one attribute (as well as size, which seems to be a fairly common requirement) ?
If a product has more than one attribute, then maybe it is actually a different product? (which is what I think oddz was getting at)
clothing
======
product : polo shirt with pocket
attribute : red
size : large
wine
====
product : Chateau Plonk 2003
attribute : red
size : 750ml
Here is a very long winded discussion where I learned about Functional Depency and natural keys which it looks as though I am advocating, or at least suggesting you consider rather than going for normalization. Apologies for the way it jumps around.
I don't know exactly how FD impacts upon domain models, but have a hunch it might make things easier.
One problem I can see right off is when a user wants to add a new "size", its not a matter of "select * from size" to show a droplist, which is what you'd normall(ized)y do.
-
Jun 18, 2009, 13:10 #12
- Join Date
- Dec 2002
- Location
- Ann Arbor, MI (USA)
- Posts
- 648
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I think that's the heart of the issue. How to deal with a variable number of attributes.
As attributes increase, number of products increase exponentially. If you have red, blue, and green shirts, and shirts with pockets and shirts without, you have a total of 6 products. Which is fine, but if you have a hundred products like this, a user is not going to want to browse through every variation of every product, nor is the client going to want to create a separate page with a separate description and a separate photo for every variation of every product. Of course this is on a spectrum: a site like newegg really doesn't have very many variations in products. If they do, they have a separate 'blue' or 'black' version-- a different product, as you said above. But you go to a clothing site, and there are many options for sizes and colors and styles for each individual product. If I'm making a system that caters to both types of sites, my instinct is to make the more flexible solution (the clothing site) and dumb things down for the simpler one.
Also-- making a new product every time something changes means duplicating all of the things that don't change. In objects I can extend a base class, but in the database the best tool I have for duplication is normalization.
I'll check it out.
-
Jun 19, 2009, 09:08 #13
- Join Date
- May 2004
- Location
- Central USA
- Posts
- 806
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
The complication here is compounded by the fact that each variation of the product needs a different SKU/UPC code so the order can be filled accurately. So even if only ONE thing is changed like color, all the sudden a new SKU must be defined because it's a different product. Nobody likes duplicating a bunch of products, but it's exactly the reason why that route is often chosen.
If you come up with an easy elegant solution to this problem, please let me know!Stackbox CMS - Full edit-on-page drag-and-drop CMS
Autoridge - Vehicle information & maintenance part numbers
Twitter | Blog | Online Javascript Compressor
-
Jun 20, 2009, 04:55 #14
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I was thinking recently of the best way to tackle the following problem, which is similar if not the same problem as you're facing allspiritseve...
If you have a product, which could have a variable number of attributes, a variable number of values per attribute, with each attribute value potentially decreasing or increasing the price of the overall product, how do you store this in a database? I thought of a couple of solutions straight up, but none of them achieved all the goals I was after. I eventually thought of the following solution which only requires two tables, while at the same time allows a lot of flexibility...
Code:------------------ Products ------------------ ID Title Base_Price ---------------------- Product Attributes ---------------------- ID Product_ID Attribute Value Price_Diff Order ID Title Base_Price -- ----- ---------- 45 T-Shirt $15 ID Product_ID Attribute Value Price_Diff Order -- ---------- --------- ----- ---------- ----- 384 45 colour blue +$0 1 385 45 colour green +$0 0 386 45 colour orange +$0 2 387 45 size small +$0 0 388 45 size medium +$1 1 389 45 size Large +$2 2 390 45 material Velvet +$5 2 391 45 material Cotton +$0 0 392 45 material Polyester -$3 1
I'm yet to find any flaw with this setup (but please by all means try and find one). Querying the product attributes table should be reasonably quick as long as you have the first 3 columns indexed (or 4 depending on how you plan to use the table). On that note, I'm not sure how you would manage SKU's if every combination needed a unique SKU. I guess in such a circumstance where SKU's are required, you really would need to treat every attribute combination as a completely separate product. I also haven't thought of yet, how you could stop/block certain combinations (maybe an exclude column in the product_attr. table which references different product_attr. ID's?).
EDIT: I just thought of a possible way to handle SKU's without modifying the above solution to the original problem. All it would involve is having a separate SKU lookup table. This table would be relatively simple with only two fields. A side-effect of this also allows blocking of certain attribute value combinations if a matching SKU isn't found.
Code:------------------- SKU Lookup ------------------- SKU Attributes SKU Attributes --- ---------- FH5636 384:387:390 FH5637 384:388:390 FH5638 384:389:390 FH5639 385:387:390 FH5640 385:388:390 FH5641 385:389:390 FH5642 386:387:390 FH5643 386:388:390 FH5644 386:389:390
-
Jun 20, 2009, 06:17 #15
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
any time you put more than one value inside a column, you are violating first normal form
and that's generally a very bad idea
-
Jun 20, 2009, 07:52 #16
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
It's not like we really have a choice in this case though. Besides, in this instance we're not treating the value of the "Attributes" column in the "SKU Lookup" as multiple values; we're using the resulting concatenated string as an index to lookup the SKU.
-
Jun 20, 2009, 23:20 #17
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
What kind of models and db schema's are chosen when using tagging systems? Isn't that about the same problem basically? You have thing A and that thing can have a variable amount of Tags.
You show a blog post and all related tags (or categories) are shown.
You show a product and all related attributes are shown
There's a lot of talk about Magento being slow and EAV models being "bad". But sites like delicious and Flickr seem to scale fine, isn't it? We should probably find out what kind of models are used in those (big) sites and learn from that
-
Jun 20, 2009, 23:53 #18
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
No, it's completely different. If the products we were dealing with had static attributes, then yes, essentially it would be the same. In this scenario though, the attributes have multiple available options, and each option can potentially increase or decrease the total price of that product.
-
Jun 21, 2009, 05:13 #19
- Join Date
- Jun 2003
- Location
- Melbourne, Australia
- Posts
- 440
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
In presenting the options to the user could you populate the page with a schema akin to blogging "tags" or categories? After all, wouldn't a specific product have a limited number of options?
As for the ordering with options, is that something that could be incorporated into the schema for the order?Zealotry is contingent upon 100 posts and addiction 200?
-
Jun 21, 2009, 05:24 #20
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
You didn't understand what I meant. I am not talking about products with static attributes. I'm saying, what if you see those variable amount of attributes the same way as tags (or categories) can be seen in apps like Flickr. Those tags are variable as well.
Flickr photo X:
Tag A, Tag B, Tag C, etc
Flickr photo X:
Tag A, Tag D, Tag F, etc
Product X
Attribute A, Attribute B, Attribute C, etc
Product X
Attribute C, Attribute D, Attribute K, etc
If you'd look at possible db tables:
PHP Code:Table Products (=Users)
1 T-shirt
2 Sweater
3 Jeans
4 Socks
Table Attributes (=Tags)
1 Color Green
2 Color Red
3 Size 2l
4 Size 1kg
Table Product2Attribute (=User2Tag)
1 1
1 3
2 4
2 9
3 3
-
Jun 21, 2009, 05:27 #21
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I'm sorry, that's not making much sense to me.
I'll assume you're talking about when a customer places an order, and not about the sorting order of the available options.
For orders, you'd have an orders table, an ordered_products table, and a third, ordered_products_attributes table. The orders table stores information about the order (eg. ID, Customer Details, Shipping, Comments), the ordered_products table stores the products that were ordered (eg. ID, Product ID,Name, Final Price), and finally, the ordered_products_attributes table would store the selected product attributes in a similar manner to the Product Attributes table mentioned in my previous post. It's pretty simply really.
-
Jun 21, 2009, 05:58 #22
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Oh ok, I see what you mean now. I guess both methods would work. However your method I think would eventually fall apart, or become convulated. For example, if you were selling clothing, and one of the attributes you could choose was material type (or some other attribute that would increase or decrease the price of the product), what would happen if getting velvet material on pants, added an extra $10 to the total price, but only added an extra $5 to the price of a shirt. You'd get two "tags" (or attribute sets) which are exactly the same, but with different price modifiers. This could turn into a potential nightmare for whoever manages the product catalogue.
With my solution, you have complete flexibility as none of the attribute sets are grouped like tags/categories. If you're concerned from a user interface point of view, then keep in mind that you can still provide a form of multiple choice to the catalogue manager by simply generating a drop-down or a bunch of checkboxes from all the unique attributes in the Product Attributes table. In addition though, a textfield would always be available to enter any new or existing sets of attributes if multiple choice wasn't the users preference.
Not that it's a problem, but your solution also requires an extra table. In the end though, both solutions are very much alike, and I guess it would depend on the developers preference as to which one is or should be used.
-
Jun 21, 2009, 06:26 #23
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Ok I didn't deal with prizes, yet.
However your method I think would eventually fall apart, or become convulated. For example, if you were selling clothing, and one of the attributes you could choose was material type (or some other attribute that would increase or decrease the price of the product), what would happen if getting velvet material on pants, added an extra $10 to the total price, but only added an extra $5 to the price of a shirt. You'd get two "tags" (or attribute sets) which are exactly the same, but with different price modifiers. This could turn into a potential nightmare for whoever manages the product catalogue.
PHP Code:Table Products (=Users)
1 T-shirt $20
2 T-shirt $22
3 T-shirt $25
4 Sweater $30
5 Jeans $40
6 Socks $10
Table Attributes (=Tags)
1 Color Green
2 Color Red
3 Size 2l
4 Size 1kg
Table Product2Attribute (=User2Tag)
1 1
1 3
2 4
2 9
3 3
In other words, I would go the normal normalization route first. But I guess there are more solutions. I really have little experience with big e-commerce sites so don't know about the possible performance problems
-
Jun 21, 2009, 11:40 #24
- Join Date
- Dec 2002
- Location
- Ann Arbor, MI (USA)
- Posts
- 648
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
@matthjis I think that's essentially what Magento does- your attributes table is basically an EAV table. That's the most flexible solution, but as I understand it, is not optimized for searching (which is pretty important in an e-commerce site).
-
Jun 21, 2009, 14:47 #25
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I don't think that would work too well. A product could potentially have over 10 multiple choice attributes, each with potentially over 10 values - assuming they all modify the price in some way, you start hitting 1000's of possible combinations (in my solution it would only store the 100 possible values, not all the combinations). Pre-calculating all of the combinations and storing them in a table is incredibly inefficient - but on that note, if you're dealing with SKU's that's essentially what you have to do anyway. But on top of that, how would you know which product/price goes with which combination of attributes?
I just see your solution as being more complex to achieve a less flexible result - in fact I don't think it would work at all when you consider variable pricing.
Bookmarks