I have 2 mysql tables, Product and Color: Color ID ColorName 1 Red 2 Green 3 Yellow 4 Blue Products ID Color1 Color2 Color3 ProductName 1 ? ? ? Orco 2 ? ? ? Skeletor 3 ? ? ? He-Man I need to display the ColorName to web visitors when they view Products. I can do a number of things: 1) put Color.ID into Product.Color1, Product.Color2 and Product.Color3. These would be Foreign Keys and from what I read, RoR likes that. 2) Create a new table called ProductColors which would have an ID, ColorID, and ProductID columns as used in many-to-many relations. 3) Use ColorName varchar in the Products table for Color1, Color2, and Color3. My actual database has a number of tables similar to Color, where all I really need to do is link a varchar field to the Products table. - Which solution would be more efficient? Using ID''s seems like too big a hit when viewing a product. - Can RoR support using a string/varchar as a foriegn key? Ie. Take out the Color ID and make the ColorName a Primary Key and link it to the Products table? Please help Jared Let''s say a table called Color has an int column ''ID'' and a varchar column ''ColorName'' and a table called Products lists Products. Each product has 4 colors. All I need to do is display the 4 colors to visitors to the website. Why not just put the strings ''Red'' ''Green'' etc... into the Product as opposed to using the Color ID''s or even a whole new table --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 1) put Color.ID into Product.Color1, Product.Color2 and Product.Color3. > These would be Foreign Keys and from what I read, RoR likes that.This is good if you ever want to change (eg) Red to Redish. You would update Color table, record 1 ONLY.. Otherwise look at my Option 3) note. Also, this option will let you specify a collection to use in (eg) a Select list, which changes dynamically.> 2) Create a new table called ProductColors which would have an ID, > ColorID, and ProductID columns as used in many-to-many relations.This is a good idea, but adds complexity. The benefit here is that you get to add more colors to a Product dynamically. But if you will always have only 3 Colors, might as well do Option 1)> > 3) Use ColorName varchar in the Products table for Color1, Color2, and > Color3.If you choose Option 3) over Option 1), you''d have to change all records in your Products Table when updating a Color name (Red to Redish). If you miss one, you create a whole new color which you might overlook later (ie changing Red to Redish (but missing a few), then assuming you only have Redish, you skip all the ones you didn''t update, that still say Red) Also, this will force you to hard code any Collections for Select lists in the Color model. Hope that helps, Bart>webonomic wrote:> I have 2 mysql tables, Product and Color: > > Color > ID ColorName > 1 Red > 2 Green > 3 Yellow > 4 Blue > > Products > ID Color1 Color2 Color3 ProductName > 1 ? ? ? Orco > 2 ? ? ? Skeletor > 3 ? ? ? He-Man > > I need to display the ColorName to web visitors when they view > Products. > > I can do a number of things: > 1) put Color.ID into Product.Color1, Product.Color2 and Product.Color3. > These would be Foreign Keys and from what I read, RoR likes that. > > 2) Create a new table called ProductColors which would have an ID, > ColorID, and ProductID columns as used in many-to-many relations. > > 3) Use ColorName varchar in the Products table for Color1, Color2, and > Color3. > > My actual database has a number of tables similar to Color, where all I > really need to do is link a varchar field to the Products table. > > - Which solution would be more efficient? Using ID''s seems like too > big a hit when viewing a product. > - Can RoR support using a string/varchar as a foriegn key? Ie. Take > out the Color ID and make the ColorName a Primary Key and link it to > the Products table? > > Please help > > Jared > > Let''s say a table called Color has an int column ''ID'' and a varchar > column ''ColorName'' and a table called Products lists Products. Each > product has 4 colors. All I need to do is display the 4 colors to > visitors to the website. Why not just put the strings ''Red'' ''Green'' > etc... into the Product as opposed to using the Color ID''s or even a > whole new table--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
But if I made the ColorName the Primary Key of the color table which links to the varchar string Color1 in the Products table, would this not avoid having things hardcoded? Thanks for the response. Jared On Jan 8, 12:33 pm, "Bart" <b...-ERYYqGLKSZVWk0Htik3J/w@public.gmane.org> wrote:> > 1) put Color.ID into Product.Color1, Product.Color2 and Product.Color3. > > These would be Foreign Keys and from what I read, RoR likes that.This is good if you ever want to change (eg) Red to Redish. You would > update Color table, record 1 ONLY.. Otherwise look at my Option 3) > note. > Also, this option will let you specify a collection to use in (eg) a > Select list, which changes dynamically. > > > 2) Create a new table called ProductColors which would have an ID, > > ColorID, and ProductID columns as used in many-to-many relations.This is a good idea, but adds complexity. The benefit here is that you > get to add more colors to a Product dynamically. But if you will > always have only 3 Colors, might as well do Option 1) > > > > > 3) Use ColorName varchar in the Products table for Color1, Color2, and > > Color3.If you choose Option 3) over Option 1), you''d have to change all > records in your Products Table when updating a Color name (Red to > Redish). If you miss one, you create a whole new color which you might > overlook later (ie changing Red to Redish (but missing a few), then > assuming you only have Redish, you skip all the ones you didn''t update, > that still say Red) > Also, this will force you to hard code any Collections for Select lists > in the Color model. > > Hope that helps, > Bart > > > > webonomic wrote: > > I have 2 mysql tables, Product and Color: > > > Color > > ID ColorName > > 1 Red > > 2 Green > > 3 Yellow > > 4 Blue > > > Products > > ID Color1 Color2 Color3 ProductName > > 1 ? ? ? Orco > > 2 ? ? ? Skeletor > > 3 ? ? ? He-Man > > > I need to display the ColorName to web visitors when they view > > Products. > > > I can do a number of things: > > 1) put Color.ID into Product.Color1, Product.Color2 and Product.Color3. > > These would be Foreign Keys and from what I read, RoR likes that. > > > 2) Create a new table called ProductColors which would have an ID, > > ColorID, and ProductID columns as used in many-to-many relations. > > > 3) Use ColorName varchar in the Products table for Color1, Color2, and > > Color3. > > > My actual database has a number of tables similar to Color, where all I > > really need to do is link a varchar field to the Products table. > > > - Which solution would be more efficient? Using ID''s seems like too > > big a hit when viewing a product. > > - Can RoR support using a string/varchar as a foriegn key? Ie. Take > > out the Color ID and make the ColorName a Primary Key and link it to > > the Products table? > > > Please help > > > Jared > > > Let''s say a table called Color has an int column ''ID'' and a varchar > > column ''ColorName'' and a table called Products lists Products. Each > > product has 4 colors. All I need to do is display the 4 colors to > > visitors to the website. Why not just put the strings ''Red'' ''Green'' > > etc... into the Product as opposed to using the Color ID''s or even a > > whole new table--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 2) Create a new table called ProductColors which would have an ID, > ColorID, and ProductID columns as used in many-to-many relations.I''d go this way, with some modifications. Table would be colors_products (alphabetical order), no id column (because it is a join table). has_and_belongs_to_many[1] would take care about the rest. [1] http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html#M000533 Simple and flexible, IMHO. Regards, Rimantas -- http://rimantas.com/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---