Topic: Product model with a twist of join tables

Hi,
I'm learning Rails to try and build a humble website for our products with some basic e-commerce functionality. I've got very limited experience with designing databases up fom scratch and was hoping someone could give me some pointers on how to improve my structure.

We sell design furniture and each product can have many options, and it can also have many modules (such as a multi-piece sofa). To be compatible with the tutorials I have on e-commerce I've connected these three different types of stuff in one table, items (which also holds default price).

An item (either product, module or option) can have many styles (a combination of material, color and finish) and a style can also have many products. I always need to track if there is a priceincrease from selecting another style and sometimes need to track which component (i.e. a leg or seat) each style is applicable to.

I also need a way to connect items with pictures, which can sometimes hold many products. It is also necessary to register which style (and sometimes which component-style combo) is in the  picture.

This is my proposed structure for modeling our products:

http://www.vonsydow.tv/filer/db.png

I somehow get the feeling that it's not very optimized and I was hoping that someone might have some input on how to improve the structure. These are my own thoughts on the matter:

* I get the feeling that my item-table might be a bit overkill, maybe I should go for registering modules and options in the product-table instead and use a type/parent_id-reference instead (similar to what has_attachment does with thumbnails).

* Perhaps I can merge the Inspiration_Pictures and Item_Pictures into one table, since I'm going to do the same stuff with them in terms of resizing etc. ?

* Maybe I should split up my Items_Styles-join table and not clutter up the join-table with extra info?

I appreciate any input! (even if it's about mistakes in my modeling-diagram hahaha...)

Thanks!
g.

Last edited by Gustav (2007-03-20 10:13:51)

Re: Product model with a twist of join tables

That's quite a complex design you have there. smile

Gustav wrote:

* I get the feeling that my item-table might be a bit overkill, maybe I should go for registering modules and options in the product-table instead and use a type/parent_id-reference instead (similar to what has_attachment does with thumbnails).

That was the initial approach I was thinking when I read your description. It doesn't look like modules and options have their own attributes, so I think this is a good solution.

However, a parent_id column would be one-to-many where currently it looks like you have many-to-many for modules. In other words, a given piece of furniture can exist in many modules. If you need this, a simple parent_id (acts_as_tree) probably won't work for you. However, a self referencing join table probably will, and I think it's still a simpler approach than what you have currently.

As for the Options, I'm not sure what exactly they are and how they differ from modules. Actually I imagine they would be more like styles?

Gustav wrote:

* Perhaps I can merge the Inspiration_Pictures and Item_Pictures into one table, since I'm going to do the same stuff with them in terms of resizing etc. ?

Definitely. Looking at the diagram you can see all picture tables have almost exactly the same structure. Merge all of these into one and use polymorphic association so they can belong_to various things (styles, products, etc.).

On second thought, polymorphic association may not even be necessary, images appear to always be related to styles in some way.

Gustav wrote:

* Maybe I should split up my Items_Styles-join table and not clutter up the join-table with extra info?

Generally, the more tables you have the more complexity there will be and the fewer tables you have the more duplication there will be. Database design is all about finding the sweet balancing point between these two. I don't know too much about your problem domain, but at the moment I would say it is more complex than it needs to be. If you apply the above points it sounds like things will become simpler - fewer tables.

Is it possible to merge the components, items_styles, and styles tables all into one "styles" table? Will this create a lot of duplication?

If you can do all of this, things will start to become much simpler. Something like this maybe:

class Product < ActiveRecord::Base
  has_many :styles
  has_and_belongs_to_many :products # roughly (for modules)
end

class Style < ActiveRecord::Base
  belongs_to :product
  has_many :images
  belongs_to :color # not shown
  belongs_to :material # not shown
  belongs_to :finish # not shown
end

class Image < ActiveRecord::Base
  belongs_to :style
end

# add STI and/or Polymorphic Association to images if you need it


I'm sure this is too simple, but it's good to see the extreme. Take a look at your requirements and interface mock ups. In what ways specifically does this design not work? Write them down and it will be clearer where more tables need to be added.

Railscasts - Free Ruby on Rails Screencasts

Re: Product model with a twist of join tables

Ah Ryan... my hero wink As always you provide invaluable input, everything looks alot neater (and better) now!

http://www.vonsydow.tv/filer/db2.png

To clarify; the difference between a module and an option is that a module is combined with other modules lego-style to create a product, where as options are minor changes that can be made to a product, optionally (such as changing the foot of your lamp).

Regarding mashing up everything into one styles table I'm afraid there will be plenty of duplication so I think I'll keep it for now, though I've changed the name of my items-styles join table to "Stylings", to reflect that it's more than a plain HABTM join table.

For further clarity, I've also changed the name of the style_pictures table to "Swatches", which is actually closer to what it is. I've also deleted style_id from swatches and instead insert a swatch_id in my Stylemodel. Don't ask me why, it just feels better smile

I would gladly have all uploaded images in the same table, but has_attachment forces me (?) to create multiple models (and also multiple tables, I guess?) if I want the images resized differently (for instance the swatches are resized differently than product images).

class Product < ActiveRecord::Base
        acts_as_tree
        has_many         :stylings
        belongs_to     :brand                     # not shown in diagram
        belongs_to     :product_type     # not shown in diagram
end

class Stylings < ActiveRecord::Base
        has_and_belongs_to_many :images
        belongs_to     :product
        belongs_to    :style
        belongs_to     :component
end

class Image < ActiveRecord::Base
        has_and_belongs_to_many :stylings
        has_attachment
end

class Style < ActiveRecord::Base
        has_many        :stylings
        belongs_to     :swatches
        belongs_to     :color # not shown
        belongs_to     :material # not shown
        belongs_to     :finish # not shown
end


I'm trying to map my models against UI as we speak, thanks a million!

Re: Product model with a twist of join tables

Looks much better!

Gustav wrote:

Regarding mashing up everything into one styles table I'm afraid there will be plenty of duplication so I think I'll keep it for now, though I've changed the name of my items-styles join table to "Stylings", to reflect that it's more than a plain HABTM join table.

Good choice, I love simple one word table names. smile

Gustav wrote:

I would gladly have all uploaded images in the same table, but has_attachment forces me (?) to create multiple models (and also multiple tables, I guess?) if I want the images resized differently (for instance the swatches are resized differently than product images).

Hmm, I wonder if single table inheritance can solve this. However, the two are related in completely different ways, so I think keeping them separate is fine.

Railscasts - Free Ruby on Rails Screencasts