Hello fellow Railers I''ll be building a product catalogue soon. I''ve been doing a bit of application design in my head and there''s one problem that I''d like to have some feedback and tips on. This product catalogue will have products, of course, but a given product can come in different variants, so-called articles. A product''s articles differs on their properties. For instance, the product "fancy t-shirt" may have variations on size and color. Different articles would have different sizes and colors, and articles have article numbers too (a string, might be anything depending on the company and product in question). However, some completely different product, like for instance a pair of jeans, would have properties such as height, waist. Different products, different properties. What I''m wondering is how to design the database here. There are products, products have variations, different variations for every product. There is no legacy database, any solution will work. Just FYI: the main use for these properties is just to have them show in a table in the product presentation view. Any tips are welcome, thanks. Regards, Tomas Jogin
If you need to put it in the database, I''d just create a table with 4 columns, id, product_id, name, value. You put the name of the property and the value in there and create a has_many relationship to it in your product model. Another possible technique you could employ is nested sets: http://www.dbazine.com/oracle/or-articles/tropashko4 You might want to look at nested sets for this, I am not sure, I have never used them and am not positive, but I think this is what they''re for, representing tree structures for very fast queries. Perhaps someone else can clarify. -Jeff ----- Original Message ----- From: "Tomas Jogin" <tomasj-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> To: <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> Sent: Wednesday, August 24, 2005 2:12 PM Subject: [Rails] product catalogue design problem Hello fellow Railers I''ll be building a product catalogue soon. I''ve been doing a bit of application design in my head and there''s one problem that I''d like to have some feedback and tips on. This product catalogue will have products, of course, but a given product can come in different variants, so-called articles. A product''s articles differs on their properties. For instance, the product "fancy t-shirt" may have variations on size and color. Different articles would have different sizes and colors, and articles have article numbers too (a string, might be anything depending on the company and product in question). However, some completely different product, like for instance a pair of jeans, would have properties such as height, waist. Different products, different properties. What I''m wondering is how to design the database here. There are products, products have variations, different variations for every product. There is no legacy database, any solution will work. Just FYI: the main use for these properties is just to have them show in a table in the product presentation view. Any tips are welcome, thanks. Regards, Tomas Jogin _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Tomas Jogin wrote:>Hello fellow Railers > >I''ll be building a product catalogue soon. I''ve been doing a bit of >application design in my head and there''s one problem that I''d like to >have some feedback and tips on. > >This product catalogue will have products, of course, but a given >product can come in different variants, so-called articles. A >product''s articles differs on their properties. For instance, the >product "fancy t-shirt" may have variations on size and color. >Different articles would have different sizes and colors, and articles >have article numbers too (a string, might be anything depending on the >company and product in question). However, some completely different >product, like for instance a pair of jeans, would have properties such >as height, waist. Different products, different properties. > >What I''m wondering is how to design the database here. There are >products, products have variations, different variations for every >product. There is no legacy database, any solution will work. > >Just FYI: the main use for these properties is just to have them show >in a table in the product presentation view. > >Any tips are welcome, thanks. > >Regards, >Tomas Jogin >_______________________________________________ >Rails mailing list >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >http://lists.rubyonrails.org/mailman/listinfo/rails > > >This may/may not cause some performance hiccups based on the amount data, but you could do something like this: Have a many-to-many relationship between a product table and an attribute table. For example: product table: id name attribute table id name attribute_values table id attribute_id name product_attribute table product_id attribute_id Just a quick thought. Post a reply if this is way off base or need more info scott. -- Scott F. Walter Scott F. Walter Principal Consultant Vivare, Inc. E: scott.walter-uosFFu51klvQT0dZR+AlfA@public.gmane.org E: scott-APWf0AbNa2kIjDr1QQGPvw@public.gmane.org Visit scottwalter.com <http://scottwalter.com> --Point. Click. Explore!
A given product has a set of attributes, but that product itself does not have the values on those attributes, the product''s articles have the values. The product "t-shirt" has the attributes "size" and "color", but it has no values. The product article "T23" might have the attribute values "XL" and "lime green". product table: id name article table: id article_no product_id attribute table id name product_id article_attribute table id name attribute_id How''s that? Also, I want the administrator to be able to sort the attributes in whichever order he/she wants, so they show up in that order in the article table in the product view. Or, maybe the articles should be derived from the product using STI? Any idea if this is a good fit for STI? I find it kind of odd that in any product catalogue tutorial or demo, the data structure representing the product (or total lack thereof) is so dead-simple that it is only useful in the very simplest of cases. Someone here must have designed and built a product catalogue with a more complex database structure, no? Regards, Tomas Jogin On 8/24/05, Scott F. Walter <scott.walter-uosFFu51klvQT0dZR+AlfA@public.gmane.org> wrote:> This may/may not cause some performance hiccups based on the amount > data, but you could do something like this: > > Have a many-to-many relationship between a product table and an > attribute table. For example: > > product table: > id > name > > attribute table > id > name > > attribute_values table > id > attribute_id > name > > product_attribute table > product_id > attribute_id > > > > > Just a quick thought. Post a reply if this is way off base or need more > info > > scott. > -- > > Scott F. Walter Scott F. Walter > Principal Consultant > Vivare, Inc. > > E: scott.walter-uosFFu51klvQT0dZR+AlfA@public.gmane.org > E: scott-APWf0AbNa2kIjDr1QQGPvw@public.gmane.org > Visit scottwalter.com <http://scottwalter.com> --Point. Click. Explore! > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On 25.8.2005, at 12.31, Tomas Jogin wrote:> A given product has a set of attributes, but that product itself does > not have the values on those attributes, the product''s articles have > the values. The product "t-shirt" has the attributes "size" and > "color", but it has no values. The product article "T23" might have > the attribute values "XL" and "lime green". > > product table: > id > name > > article table: > id > article_no > product_id > > attribute table > id > name > product_id > > article_attribute table > id > name > attribute_id > > How''s that?This looks pretty fine. Although in articles_attributes table you should have: articles_attributes article_id attribute_id value The name of the attribute is in attributes table already, right? So you just need to store the value here. I would also add column ''position'' to attributes table. Then you can use ActiveRecord''s acts_as_list and you have an easy way for admins to edit the order of attributes for a given product.> Also, I want the administrator to be able to sort the > attributes in whichever order he/she wants, so they show up in that > order in the article table in the product view. > > Or, maybe the articles should be derived from the product using STI? > Any idea if this is a good fit for STI?I don''t feel it fits that well in this situation because you need to have separate tables for the attributes anyway. A Product object has in this case a bit different semantics than an article so article is not really a child of a product here. This solution still doesn''t take into account the types of the attribute values, they are probably stored as varchar. There''s also no way to restrict the possible values for a given attribute. This could be solved by using the serialize function in AR and then storing the values in a text field in the attributes table. But anyway, you probably need to give some thought to that, too. //jarkko -- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 8/25/05, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org> wrote:> I would also add column ''position'' to attributes table. Then you can use > ActiveRecord''s acts_as_list and you have an easy way for admins to edit the > order of attributes for a given product.True, that solves that bit.> > Or, maybe the articles should be derived from the product using STI? > > Any idea if this is a good fit for STI? > > I don''t feel it fits that well in this situation because you need to have > separate tables for the attributes anyway. A Product object has in this case > a bit different semantics than an article so article is not really a child > of a product here.True, different semantics, but on the other hand it solves one problem: Some products have no articles, like perhaps a book which doesn''t come in variants, while other products do, like the aforementioned t-shirt which comes in different sizes and colors. When you want to add the article to the cart, but the product doesnt have articles, you would have to use an "invisible" article and add it to the cart, right? Whereas if articles were derived from product, the problem would just not be there? Any other ways to solve this?> This solution still doesn''t take into account the types of the attribute > values, they are probably stored as varchar. There''s also no way to restrict > the possible values for a given attribute. This could be solved by using the > serialize function in AR and then storing the values in a text field in the > attributes table. But anyway, you probably need to give some thought to > that, too.That''s cool I think, attributes and attribute values would always be arbitrary strings. Besides, attributes and attribute values would only be used for _display_ in a table on the product view, not for anything more complex. Regards, Tomas Jogin
On 25.8.2005, at 13.33, Tomas Jogin wrote:> On 8/25/05, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org> wrote: >> >> I don''t feel it fits that well in this situation because you need >> to have >> separate tables for the attributes anyway. A Product object has in >> this case >> a bit different semantics than an article so article is not really >> a child >> of a product here. >> > > True, different semantics, but on the other hand it solves one > problem: Some products have no articles, like perhaps a book which > doesn''t come in variants, while other products do, like the > aforementioned t-shirt which comes in different sizes and colors. > > When you want to add the article to the cart, but the product doesnt > have articles, you would have to use an "invisible" article and add it > to the cart, right?Right, but I don''t think that would be too much of a problem. You could e.g. automatically create a single "blank" variant if the admin so chooses.> Whereas if articles were derived from product, the > problem would just not be there? Any other ways to solve this?The problem as I see it, is that an article doesn''t really inherit a product. If it did, it would have all the same attributes as product, including a has_many relationship to Attribute class, plus some additional vars/methods. This does not hold true in your case. I don''t say this couldn''t be done with the STI implementation in AR because it''s pretty loose in its requirements. I just think it would be a pervertion of an object inheritance model. But this is just my elitist opinion so feel free to ignore it if you find that using STI solves your problem well. //jarkko -- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
> Right, but I don''t think that would be too much of a problem. You could e.g. > automatically create a single "blank" variant if the admin so chooses. > > Whereas if articles were derived from product, the > problem would just not be there? Any other ways to solve this? > > The problem as I see it, is that an article doesn''t really inherit a > product. If it did, it would have all the same attributes as product, > including a has_many relationship to Attribute class, plus some additional > vars/methods. This does not hold true in your case. > > I don''t say this couldn''t be done with the STI implementation in AR because > it''s pretty loose in its requirements. I just think it would be a pervertion > of an object inheritance model. But this is just my elitist opinion so feel > free to ignore it if you find that using STI solves your problem well.I''m gonna follow your advice, thanks a whole lot for your feedback; my product will be better for it. The "invisible" article felt a bit like a dirty solution to me, but I guess it''s indeed favorable over using STI in this case. Best regards, Tomas Jogin
On 8/24/05, Tomas Jogin <tomasj-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hello fellow Railers > > I''ll be building a product catalogue soon. I''ve been doing a bit of > application design in my head and there''s one problem that I''d like to > have some feedback and tips on. > > This product catalogue will have products, of course, but a given > product can come in different variants, so-called articles. A > product''s articles differs on their properties. For instance, the > product "fancy t-shirt" may have variations on size and color. > Different articles would have different sizes and colors, and articles > have article numbers too (a string, might be anything depending on the > company and product in question). However, some completely different > product, like for instance a pair of jeans, would have properties such > as height, waist. Different products, different properties. > > What I''m wondering is how to design the database here. There are > products, products have variations, different variations for every > product. There is no legacy database, any solution will work. > > Just FYI: the main use for these properties is just to have them show > in a table in the product presentation view. > > Any tips are welcome, thanks.We have a commercial shopping cart system (not in rails) where we simply added a second table like so for product options: table product_options sku varchar name varchar value varchar price numeric Chris
On 8/24/05, Scott F. Walter <scott.walter-uosFFu51klvQT0dZR+AlfA@public.gmane.org> wrote:> This may/may not cause some performance hiccups based on the amount > data, but you could do something like this: > > Have a many-to-many relationship between a product table and an > attribute table. For example: > > product table: > id > name > > attribute table > id > name > > attribute_values table > id > attribute_id > name > > product_attribute table > product_id > attribute_idI''ve been working on implementing this since you gave this response. What I need to do is generate a parts list, with the attributes included in the list. To do this I run three queries: 1. parts query 2. attributes query (to determine which attribute headings to display) 3. attribute_values query (which contains all the part_attribute_values) I then display the attributes for a given part like this: @parts.each do |p| ... @attributes.each do |a| data << "<td>" @part_attributes.each do |v| if v.id.to_i == p.id and a.name == v.name data << v.value end end data << "</td>" end ... end The one problem I''ve found with this, is that it''s really slow. Does anyone have any suggestions for improving this? James