Hi there, I''m at a loss... I was on the #ROR channel last night and spoke to a really helpful person (Defiler) who gave me some direction on building a shopping cart. Unfortunately, I wasn''t able to successfully get to where I need to go. This is the predicament I am currently finding myself in: I''m building a shopping cart for a client''s new website. They sell clothing, which means that for each product (ie: T-Shirt), there are various sizes and colours (we''ll call these combinations). At first, the SKU #''s provided by the client were not specific to the combination each product offers. So, the related DB tables were essentially this: Table Products: ==========id | sku | title | price | etc... Table Colors: ========id | color Table Sizes: ========id | sizes Table Colors_Products: ================color_id | product_id Table Products_Sizes: ===============product_id | size_id Everything was fine and dandy until I got to the point where I wanted each combination of a product to appear as a separate line item in the shopping cart (the natural way to display items). There was no way for me to determine the uniqueness of a product, since they all shared the same SKU#. There was no distinguishing field in the product table. What I''ve since done is try to generate the SKU# based on form input fields on the Create Product form. There are currently radio button groups for size, colour, etc. From these chosen fields, I am trying to generate the SKU# for each product combination. The problem I''m running into, however, is in trying to create Product records in the database. I''m pretty confused at the moment on how I should proceed. I had a "half-assed" method in my controller (intend to move the final version to a helper) that would generate a SKU# based on the fields, but it did not work as cleanly/smoothly as I had hoped. Essentially, what I had was a method that loops through all the colors and sizes passed from the form params, and calls "@product = Product.new(product)". It seemed to work until I would try and re-submit the same form; calling "@product.save" would crash the application. I expected it to not work since the SKU# should be unique for each product, but could not find a way to "safely" alert the user of an duplicate entry. I guess what I''m asking for is some direction on how I can best create this product/cart system. I''m not asking for the full code, but rather some insight from more fellow developers who are more experienced with shopping cart solutions. I would greatly appreciate anything anyone can provide. Kind regards, Frank _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 10/5/05, Frank Manno <frankslists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi there, > > I''m at a loss... > > I was on the #ROR channel last night and spoke to a really helpful person > (Defiler) who gave me some direction on building a shopping cart. > Unfortunately, I wasn''t able to successfully get to where I need to go. > This is the predicament I am currently finding myself in: > > I''m building a shopping cart for a client''s new website. They sell > clothing, which means that for each product (ie: T-Shirt), there are various > sizes and colours (we''ll call these combinations). At first, the SKU #''s > provided by the client were not specific to the combination each product > offers. So, the related DB tables were essentially this: > > Table Products: > ==========> id | sku | title | price | etc... > > Table Colors: > ========> id | color > > Table Sizes: > ========> id | sizes > > Table Colors_Products: > ================> color_id | product_id > > Table Products_Sizes: > ===============> product_id | size_id > > Everything was fine and dandy until I got to the point where I wanted each > combination of a product to appear as a separate line item in the shopping > cart (the natural way to display items). There was no way for me to > determine the uniqueness of a product, since they all shared the same SKU#. > There was no distinguishing field in the product table. > > What I''ve since done is try to generate the SKU# based on form input fields > on the Create Product form. There are currently radio button groups for > size, colour, etc. From these chosen fields, I am trying to generate the > SKU# for each product combination. > > The problem I''m running into, however, is in trying to create Product > records in the database. I''m pretty confused at the moment on how I should > proceed. I had a "half-assed" method in my controller (intend to move the > final version to a helper) that would generate a SKU# based on the fields, > but it did not work as cleanly/smoothly as I had hoped. Essentially, what I > had was a method that loops through all the colors and sizes passed from the > form params, and calls "@product = Product.new(product)". It seemed to work > until I would try and re-submit the same form; calling "@product.save" would > crash the application. I expected it to not work since the SKU# should be > unique for each product, but could not find a way to "safely" alert the user > of an duplicate entry. > > I guess what I''m asking for is some direction on how I can best create this > product/cart system. I''m not asking for the full code, but rather some > insight from more fellow developers who are more experienced with shopping > cart solutions. I would greatly appreciate anything anyone can provide.You might need to add a ''SalableProduct'' or ''ProductForSale'' to your shopping cart. A ''ProductForSale'' would probably have a Product, a ProductColor, and a ProductSize. In fact, you probably don''t need the join tables. In the ProductForSale table, you could have a product_id, a price, a quantity (in stock), a size, and a color. Then you could keep track of what shirts you have in stock in what size and in what color.
Sorry, this wasn''t forwarded onto the list: Cuong: That''s something along the lines of what I was thinking about this afternoon... To introduce an additional table (ie: ProductSKU or some other name), that would essentially be a composite key made up of product id, color, size, etc. On 10/5/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: It would help if you introduce a new >model "ProductOffering" (which>normally called Sku but your client already uses that term) and >references it in your shopping cart >table product_offerings: >id | color | size | product_idJoe: I see where you''re going (same idea as Cuong), except I''m not too sure about not needing the join tables. Not that I don''t agree... I just don''t understand why I wouldn''t need them. Would you mind elaborating some more? I''d really appreciate it. The way I was thinking about going was to introduce this ProductSKU table/class, but still keep the Colors, Sizes, Colors_Products, Products_Sizes join tables, so that I can easily query product colors and sizes without having to hit the ProductSKU table. Or is there something that I wouldn''t be able to drill down into detail without querying the ProductSKU table for size/color information? Thanks for your help thus far, guys... It''s really made a difference! Frank On 10/5/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > You might need to add a ''SalableProduct'' or ''ProductForSale'' to your > shopping cart. A ''ProductForSale'' would probably have a Product, a > ProductColor, and a ProductSize. > > In fact, you probably don''t need the join tables. In the > ProductForSale table, you could have a product_id, a price, a quantity > (in stock), a size, and a color. Then you could keep track of what > shirts you have in stock in what size and in what color. >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Okay... So I took Cuong''s and Joe''s advice and re-worked the Database design last night. I now have a ProductSku table (essentially the product offerings), that contains the following colums: Table: ProductSku =============id | product_id | size_id | color_id | inscription_id | inscription_color_id Inscription is the text that appears on garments (ie: silkscreened text), and inscription_color is the available colours for that inscription. I have two separate tables (Inscription and Inscription_Colors) that have a M:M relationship between the two; so I also have an "Inscription_Inscription_Colors" table (I know, looks confusing, but it works). The problem I''m having is when I create a new product, I choose the available sizes, colors, inscriptions and inscription colors that the product is available in (not all products have the same inscriptions, and not all inscriptions come in the same inscription colors). After submitting the form to create a new product, everything in the ProductSku table works out fine except for the "inscription_id" and "inscription_color_id" fields. For some reason, the only inscription_id and inscription_color_id that are inserted for each ProductSku record is "1" and "1": Sample Record: id = 1, product_id = 4, size_id = 2, color_id = 1, inscription_id = 1, inscription_color_id = 1 If I''ve chosen multiple inscriptions and multiple inscription colors, they are not reflected in the ProductSku table. I know... it''s a complicated mess; but this is how the business operates. The client is a wholesaler, and not all products come in the same configurations. I''m stuck on how to proceed to ensure that the inscription and inscription_colors are matched up against what comes in through the form. Do I need to somehow link the Product model to the Inscriptions_Inscription_Colors table? I just want to ensure that every combination of products are covered when inserting new records. Any insight would be extremely helpful... Thanking you in advance, Frank On 10/6/05, Frank Manno <frankslists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Sorry, this wasn''t forwarded onto the list: > > Cuong: > > That''s something along the lines of what I was thinking about this > afternoon... To introduce an additional table (ie: ProductSKU or some other > name), that would essentially be a composite key made up of product id, > color, size, etc. > > > On 10/5/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: It would help if you > introduce a new >model "ProductOffering" (which > >normally called Sku but your client already uses that term) and > >references it in your shopping cart > >table product_offerings: > >id | color | size | product_id > > Joe: > > I see where you''re going (same idea as Cuong), except I''m not too sure > about not needing the join tables. Not that I don''t agree... I just don''t > understand why I wouldn''t need them. Would you mind elaborating some more? > I''d really appreciate it. > > The way I was thinking about going was to introduce this ProductSKU > table/class, but still keep the Colors, Sizes, Colors_Products, > Products_Sizes join tables, so that I can easily query product colors and > sizes without having to hit the ProductSKU table. Or is there something that > I wouldn''t be able to drill down into detail without querying the ProductSKU > table for size/color information? > > > Thanks for your help thus far, guys... It''s really made a difference! > Frank > > On 10/5/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org > wrote: > > > > You might need to add a ''SalableProduct'' or ''ProductForSale'' to your > > shopping cart. A ''ProductForSale'' would probably have a Product, a > > ProductColor, and a ProductSize. > > > > In fact, you probably don''t need the join tables. In the > > ProductForSale table, you could have a product_id, a price, a quantity > > (in stock), a size, and a color. Then you could keep track of what > > shirts you have in stock in what size and in what color. > > > >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails