SitePoint Sponsor |
|
User Tag List
Results 26 to 50 of 96
-
Jun 21, 2009, 15:20 #26
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Could you also explain why you think putting prizes in the product table wouldn't work?
I would think that if you have a green T-shirt in size XL, then that's one product with one prize. So that T-shirt has an unique Id and a unique prize. Put the Id, product name and prize in one table. Following normalization rules, put the attributes in different tables with foreign keys.
One way or the other, these 1000's of possible combination's will be there. Whether you put them in one table or in 10 tables. I don't know the specific requirements of the project of the original poster. Depending on those, you would choose a very normalized approach or otherwise a less normalized one. I'm also not familiar with the prizing scheme used. Probably depending on that you could choose a different approach
@allspiritseve: yes, I can see how an AEV approach makes the search queries a bit more difficult. Maybe looking into how different tagging schema's are used in the bigger web apps like Delicious would be interesting.
A somehwat older article, but still interesting
http://www.pui.ch/phred/archives/200...nce-tests.html
-
Jun 21, 2009, 20:20 #27
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So you're basically saying, with your solution, you can't have attributes change the total price of an item (and instead, need to add a completely different product for each price combination).
The great thing about my solution (simply as an example), is that it's very user friendly. They don't need to sort through 100 different t-shirts to try and find the one with the right, size, colour and material. Instead, they can just find the t-shirt product, and select exactly what they want out of the available options. It creates a much simpler, friendlier user experience.Last edited by Wardrop; Jun 22, 2009 at 20:31.
-
Jun 21, 2009, 23:52 #28
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
No. Attributes do change the prize of the product. The only difference is in how to store the different "products/combination's" in the db.
The user friendliness has nothing to do with it. I thought we were talking about the model and database schema, not the user interface. I assume that in the application to be build the user interface and how it looks and functions is in a separate layer and completely independent from the back end.
Allspritieve: did you make any progress on your problem or find out anything new? Have you also asked on one of the db-forums?
-
Jun 22, 2009, 01:01 #29
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
You don't want to be storing all the possible combinations though. You only want to be storing the available options which result in all the different combinations.
Could you please demonstrate that then, as so far you haven't shown how that's achieved.
It has more to do with it than you obviously think. How your store your data can have a big impact on the different ways the data can be displayed.
By the way, can I ask why you refer to the price as the "prize" (if that's what you're referring to). I don't mean to have a go at you, but I want to make sure that we're both talking about the same thing here.
-
Jun 22, 2009, 03:04 #30
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
You don't want to be storing all the possible combinations though. You only want to be storing the available options which result in all the different combinations.
Could you please demonstrate that then, as so far you haven't shown how that's achieved.Code:"Master spreadsheet" 1 Shirt Green Small $20 2 Shirt Green Medium $22 3 Shirt Green Large $24 4 Shirt Blue Small $22 5 Shirt Blue Medium $24 6 Shirt Blue Large $26 7 Jeans Green Large $20 ... One way to achieve a more normalized db schema: Product 1 Shirt $20 2 Shirt $22 3 Shirt $24 4 Shirt $26 5 Shirt $30 Attributes 1 Size Small 2 Size Medium 3 Size Large 4 Color green 5 Color red 6 Color blue Product2Attributes prodId attrId 1 1 1 2 1 3
It has more to do with it than you obviously think. How your store your data can have a big impact on the different ways the data can be displayed.
By the way, can I ask why you refer to the price as the "prize" (if that's what you're referring to). I don't mean to have a go at you, but I want to make sure that we're both talking about the same thing here.
-
Jun 22, 2009, 04:03 #31
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
This example demonstrates a lack of dynamic pricing (or a flawed model if anything). What you're doing is linking an attribute value to a product with a set price, which will fail as soon as you add two separate attributes to a product, which have a conflicting price.
I'll rewrite your example using the same schema, so you hopefully understand what I'm saying.
Code:Products ------------------- 1 Shirt $20 2 Shirt $22 3 Shirt $24 4 Shirt $26 5 Shirt $30 Attributes ---------------------- 1 Size Small 2 Size Medium 3 Size Large 4 Color green 5 Color red 6 Color blue Product2Attributes ------------------ prodId attrId 1 1 2 1 3 1 2 4
Let me explain what's happening here. Seeing as though all the shirt products would essentially be treated as a single product in the front end, you'd get a conflict when someone tried to order a green shirt of any size. That's because the green colour value is linked to the $22 shirt, but all the shirt sizes are linked to the $20 shirt. So how much does the shirt cost? As you can imagine, it gets even worse when you introduce more than two attributes.
Do you see my point matthijsA?
As a simple example. If you stored a large green shirt, small red shirt and medium orange shirt as separate products in the database (so as separate rows in the products table), you would be forced to display those products as three separate items. If however you stored just a shirt as a product, and all the different attributes (size, colour) were stored in an attributes table, you'd be able to display only one item, with drop-down menus to select from the available attribute values. In both scenarios, you don't really have to many other options. The schema of the database determines how you can display the data.
-
Jun 22, 2009, 04:38 #32
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
No, I'm sorry, I don't understand it. Maybe the confusion is caused by the fact that you think that an attribute is linked to a price. But it's not. A single, unique product has only one single price.
(also, don't pay attention to the exact numbers in my example schema, they are not correct)
What is a single product? Think about a real physical t-shirt. That single t-shirt has several attributes. Color, size, material and price. Of all those attributes there's one which will always be there for every product: price. So we can leave that attribute in the db table products.
For all the other attributes we face the problem that each product can have one or more attributes. A t-shirt has maybe 3 attributes, a bottle of wine has maybe 10 different attributes. So we can't put them all in the same products table, as that would mean you would have a) too many columns b) a lot of repeating data
So therefore you create a separate table for the attributes. And a look-up table to link each single product to more attributes. One product has one price but many attributes.
.. The schema of the database determines how you can display the data
-
Jun 22, 2009, 05:04 #33
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Maybe I'm the only one who read the original post. allspiritseve said that each attribute was to have a different price. I provided a solution that allowed any number of attributes and values to be combined, while at the same time, allowing attributes to dynamically change the price of a product depending on the values that were selected.
This whole time I've been trying to prove to you that your solution didn't support such dynamic pricing, and only supported one price altering attribute (when there could potentially be many). However it's clear now that you weren't trying to achieve such a thing, which makes me ask what relevance your solution has to the original post, as the whole point of this thread I thought, was about linking prices to attributes.
On that note however, I found it odd that allspiritsteve said that your EAV-like solution was "the most flexible solution", which makes me wonder if even he knows what he's after. Maybe he didn't realise the fact that your schema only support a single price altering attribute?
-
Jun 22, 2009, 05:14 #34
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I completely disagree. You tell me then how I could display a t-shirt product, with drop-down boxes for the available attributes (colour, size, etc), if all the different t-shirts were stored as separate products with different titles (ie. Large Green T-Shirt, Small Orange T-Shirt, Large Orange T-Shirt).
The database schema (or how the data is stored, take your pick as to which wording suits you best) determines to some extent, the format the data can be displayed in. If you're storing all product variations (colour, size) as separate products, then it's virtually impossible to provide an interface which offered a number of attribute selections (drop-down menus, radio buttons, etc).
The same data, stored in different ways, alters how that data can be displayed. It's simple.
-
Jun 22, 2009, 05:20 #35
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jun 22, 2009, 05:34 #36
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
r937, this was the table I had in mind...
Code:ID Title Price -- ----- ----- 1 T-Shirt - Large Green $20 2 T-Shirt - Green Medium $20 3 T-Shirt - Small Green $19 4 T-Shirt - Orange Medium $20 5 T-Shirt - Small Orange $19 ...or even without different pricing... ID Title Price -- ----- ----- 1 T-Shirt - Large Green $20 2 T-Shirt - Green Medium $20 3 T-Shirt - Small Green $20 4 T-Shirt - Orange Medium $20 5 T-Shirt - Small Orange $20
-
Jun 22, 2009, 05:48 #37
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:ID Title Size Colour Price -- ----- ----- ------ ----- 1 T-Shirt Large Green $20 2 T-Shirt Medium Green $20 3 T-Shirt Small Green $19 4 T-Shirt Medium Orange $20 5 T-Shirt Small Orange $19
-
Jun 22, 2009, 05:53 #38
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
You just proved my point. You had to change the schema of the database in order to change how the products can be displayed. Proof that how the data is stored (despite being the same data), directly affects how the data can be displayed to the user.
-
Jun 22, 2009, 06:02 #39
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
and you just proved my point, don't design a deficient schema if you already know you want to search by size or colour
-
Jun 22, 2009, 06:03 #40
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
of course each t-shirt with different attributes has a different price. In my example solution that is also the case. I don't know why you think that isn't so.
Let us start with one single product. A t-shirt. Which is: green, large, cotton and costs $20. we fill in the db tables and end up with this:
Code:Product 1 Shirt $20 Attributes 1 Size Large 2 Color Green 3 material Cotton Product2Attributes 1 1 1 2 1 3
Now we add another t-shirt, this time also green and cotton, but size medium. Now we end up with:
Code:Product 1 Shirt $20 2 Shirt $25 Attributes 1 Size Large 2 Color Green 3 material Cotton 4 Size Medium Product2Attributes 1 1 1 2 1 3 2 4 2 2 2 3
-
Jun 22, 2009, 08:34 #41
- Join Date
- Dec 2002
- Location
- Ann Arbor, MI (USA)
- Posts
- 648
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Progress, I suppose. I don't know that I've found an ideal solution yet. I did post in the db forum: http://www.sitepoint.com/forums/show...=1#post4295122
Originally Posted by Wardrop
The fact is, both of your solutions are EAV-based. Both are flexible for storing data, but are detrimental for searching. We've established that Magento uses EAV, and we've established Magento is horribly slow. Therefore, I'm trying to find out what schema is NOT horribly slow when doing searches, while still allowing for as many attributes and values of attributes as needed.
-
Jun 22, 2009, 09:42 #42
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Have you measured performance problems for real already? (maybe in a test db full of dummy data)
I don't know anything about the inner workings of magento so can't tell why it would be slow. There could be more reasons then the db schema. And maybe, if there are performance problems caused by the db schema, there are other solutions to handle those (caching, memcache, seperate search db's, etc)
I'm reading up on tagging and category db schema's and performance, if I find anything interesting I'll post it here
-
Jun 22, 2009, 11:22 #43
- Join Date
- May 2005
- Location
- Finland
- Posts
- 608
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
You should know that a major player in the CMS industry, eZPublish, also hosts a kind of an EAV-solution – and is, incidentally enough, about as slow as a pack of mollusks put together. Not very fast indeed.
-
Jun 22, 2009, 11:42 #44
- Join Date
- Dec 2002
- Location
- Ann Arbor, MI (USA)
- Posts
- 648
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Last edited by allspiritseve; Jun 22, 2009 at 15:07.
-
Jun 22, 2009, 15:04 #45
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I'm not bashing him for thinking of an idea. I've been trying to prove that his schema doesn't fulfil the requirements you mentioned in your original post. If I posted a proposed schema (which I have), I'd personally want to know if I didn't fulfil the requirements.
@matthijsA...
I've come this far, I may as well keep going.
Your schema does not allow any more than one price changing attribute. If you had a product which had the sizes small, medium and large available, and it also had two different types of material, cotton and polyester, your schema WOULD allow the price to change depending on the size selected, but if you also wanted to charge an extra $2 for the shirt in cotton, then you wouldn't be able to. To achieve that, you'd have to create a totally separate product, and you'd have to display it like so in the frontend of the application.
In summary, my proposed schema supports fully customisable products; exactly like Dell allows when you purchase a computer from their website, you can add any number of options to your purchase, each of which add or substract from the final price. Your system on the other hand only supports a single price altering attribute with as many additional non-price altering attributes as you want). That's the point I've been trying to make.
How I understood the original post, was that allspiritsteve wanted a schema that allowed fully configurable/customisable products, with the ability to have multiple optional attributes affect the final price of that product. That's my reason for trying to prove the point I'm trying to prove - your schema does not meet those requirements. Unfortunately it's taken me many posts to try and explain that.
On a completely separate note, everyone keeps saying that EAV-like schemas slow down searching. Can anyone explain this in more detail as it doesn't make much sense to me as why this would be the case? What exactly would you be searching for which would cause the slow down.Last edited by Wardrop; Jun 22, 2009 at 20:28.
-
Jun 22, 2009, 23:47 #46
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
No. I am not sure how to explain it any better. In my schema you can have as many attributes as you want, and each combination has it's own price. Below, a shirt in 2 sizes and 2 materials, which gives us 4 variations of the product:
Code:Product 1 Shirt $20 (this is a Small, Cotton shirt) 2 Shirt $25 (this is a Large, Cotton shirt) 3 Shirt $22 (this is a Small, Polyester shirt) 4 Shirt $27 (this is a Large, Polyester shirt) Attributes 1 Size Small 2 Size Large 3 Material Cotton 4 Material Polyester Product2Attributes 1 1 1 3 2 2 2 3 3 1 3 4 4 2 4 4
And about the display in the front end: we shouldn't keep discussing that, as it's not relevant. With each schema you can display what you want in the front end.
Is this the right solution for this specific problem? I don't know, maybe not. I hope some other people will come up with other possible solutions and with their experiences in different schema's and their (dis)advantages to help allspiriteve with his problem
-
Jun 23, 2009, 01:22 #47
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
With the example you posted above, what you're doing is essentially storing possible attribute combinations, which ultimately makes the data less flexible and results in having to store more data. As I mentioned earlier, if you had a product which a lot of customisable attributes (for example, a car - which may include wheels, colour, engine, interior, etc), you could potentially have 1000's of possible combinations, all of which would have to be stored in the database.
Let me demonstrate. Let's say we have a single product (T-Shirt) which has a size attribute, which consists of the values: small ($19), medium ($20), and large ($21). Let's also say we have a material attribute which consists of the values: polyester, cotton (an extra $2), satin (as extra $4). Let me first demonstrate how this would look in my proposed schema (a schema that doesn't store possible combinations)...
Code:ID Title Base_Price -- ----- ---------- 45 T-Shirt $19 ID Product_ID Attribute Value Price_Diff Order -- ---------- --------- ----- ---------- ----- 387 45 size small 0 388 45 size medium +$1 1 389 45 size Large +$2 2 390 45 material Polyster 0 391 45 material Cotton +$2 1 391 45 material Satin +$4 2
Now let's look at how the same thing is achieved in your schema (a schema that stores possible combinations - and that also stores each attribute value which makes up those combinations as a separate row - that's the number of combinations multiplied by the number of attributes)...
Code:ID Title Price -- ----- ----- 1 Shirt $19 2 Shirt $20 3 Shirt $21 4 Shirt $21 5 Shirt $22 6 Shirt $23 7 Shirt $23 8 Shirt $24 9 Shirt $25 ID Attribute Value -- --------- ----- 1 size Small 2 size Medium 3 size Large 4 material Polyster 5 material Cotton 6 material Satin Product_ID Attribute_ID ---------- ------------ 1 1 1 4 2 2 2 4 3 3 3 4 4 1 4 5 5 2 5 5 6 1 6 6 7 3 7 5 8 2 8 6 9 3 9 6
I'm not saying your schema doesn't serve a purpose, it's just it's not a practical solution when you need to store multiple attributes with multiple price-altering values per attribute. A schema like yours used in such an application as this, would also be very slow.Last edited by Wardrop; Jun 23, 2009 at 03:50.
-
Jun 23, 2009, 02:52 #48
- Join Date
- Jun 2004
- Location
- Netherlands
- Posts
- 172
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Now we are at last getting some where. You finally seem to understand that my schema is possible. It is storing all the products/variations of products. Just in a different way.
You are correct that in my schema there are more rows. But they are rows of simple integers. In your schema, you have a lot of duplication of attribute and value names (which goes against normalization rules). But honestly, I really don't know enough about the domain model of allspiriteve's project to say what is "better". I just proposed an alternative schema. I really don't feel like or need to defend it.
-
Jun 23, 2009, 03:17 #49
- Join Date
- Mar 2002
- Location
- Bristol, UK
- Posts
- 2,240
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Wardrop, please explain to me, using your proposed schema, how choosing 'cotton' as the material could increase the price by $2 if the shirt is in medium, and by $3 if large is selected. Not that you'd necessarily need to, but I still think that's another potential shortcoming of your table structure.
Oh, and don't be fooled into thinking that more rows = less efficient, or that more rows = lack of flexibility for displaying the data at the front end. If data is stored in an efficient manner, you will be able to display it however you want. Trust me on this
-
Jun 23, 2009, 03:47 #50
- Join Date
- Oct 2006
- Location
- Queensland, Australia
- Posts
- 852
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I don't think there was a stage where I didn't understand your schema and that it was "possible", it was just so damn impractical I thought that maybe you were missing something.
I disagree, and not just because we've always disagreed :-)
The attribute column is probably the only true form of duplication in my schema (more specifically, in the attributes table), but it's absolutely pointless to separate a single column out into another table - and there's a reason I why I wouldn't pair it up with the 'value' and 'price_diff' columns to be separated into another table, let me explain in list form...
- An attribute may have a different set of values between products. For example, one product may have sizes small, medium and large, but another may only have medium and large, or short, medium and long.
- Different attributes may contain the same values. For example, the value '1 litre', may be associated with the attributes: size, capacity, content, etc.
- An attribute with the a certain set of values, which are identical between multiple products, may have different pricing modifiers. For example, we may have a t-shirt, and a pair of pants which share the same attribute and associated set of values (ie. Size, which consists of small, medium and large), but the price modifiers for the t-shirt may be +$1 for the medium and +$2 for the large, where as the price modifiers for the pair of pants may be +$2 for the medium and +$5 for the large.
As you can see, data integrity may be more at stake if those three columns WERE normalised, than if they weren't. God knows how a catalogue manager would be able to select and maintain attribute sets which only had a single difference between them. It would be extremely impractical. Take the following four attribute/value sets as an example...
Code:Size - Small (+$0) - Medium (+$1) - Large (+$2) Size - Small (+$0) - Medium (+$1) - Large (+$3) Size - Small (+$0) - Medium (+$1) Size - Short (+$0) - Medium (+$1) - Long (+$2)
Bookmarks