Hey guys, I''m putting together an app with user profiles, and each user will be able to update a section called ''quick stats''. This will involve lots of drop down lists with options like ''hair colour'', ''height'', ''Location'' etc etc. The way I m thinking about doing it is to create new table for each group of options like... ************ hair_colour id colour ************ and then in the profile table have.. ************ profiles id user_id hair_colour_id ************ It just semms like there must be a better way of doing this without creating so many tables? -- Posted via http://www.ruby-forum.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 -~----------~----~----~----~------~----~------~--~---
The way you are doing it IS best practise. These types of tables are commonly called "codes" or "lookup" tables as they are just code/description pairs. I usually prefix them with LU_ to distinguish them from the main application tables. The other way to do it would be to have a single table for all codes by adding a "type" column that would differentiate the type of code but I would advise against it. -Paul --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Paul: Could you please expand on why it''s a best practice to use separate lookup tables? Also, why do you advise against using a single table? I''ve used a single table in the past and want to know why it''s sub- optimal. Thanks, -Anthony On Dec 20, 2006, at 9:58 AM, Paul Corcoran wrote:> > The way you are doing it IS best practise. These types of tables are > commonly called "codes" or "lookup" tables as they are just > code/description pairs. I usually prefix them with LU_ to distinguish > them from the main application tables. > > The other way to do it would be to have a single table for all > codes by > adding a "type" column that would differentiate the type of code but I > would advise against it. > > -Paul > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Anthony, Normalization is the short answer. Lets say you need to add an effective_date column to one of your lookup tables because the code is only effective starting on 1/1/2007 for instance. If everything is lumped in one table all of your codes would have to take on that new attribute. This might not seem like a big deal but now your table encapuslates two different data models. Eventually you end up with code that says "if type = this do that". Yes, you could just create a new table at this point to model just the code that needs the effective_date column and leave the existing table alone. But, that is why it''s best practise to start that way. However, what you have done in the past WILL work within the limitations just outlined. -Paul --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Paul, Thanks for the explanation-- and the long answer! I appreciate stuff like this because it makes it easier to choose the correct way the next time I''m confronted with choices. I have been using single table inheritance (STI) for my lookups. When something really different comes along-- you''re right-- I do have to change to a separate table. The trade-off is that I don''t have to repeat myself for the standard stuff: all my lookups have a display, key value, effective_date range, and active flag. With many people throwing around DRY as a good idea, do you think that separate tables is always the best starting place? I think that for tables that have a low chance of being structurally changed, STI is a decent time saver. But, I''ve got to admit that my proverbial short cuts don''t always pay dividends. What''s your feeling on STI? Thanks, -Anthony On Dec 20, 2006, at 12:11 PM, Paul Corcoran wrote:> > Anthony, > > Normalization is the short answer. Lets say you need to add an > effective_date column to one of your lookup tables because the code is > only effective starting on 1/1/2007 for instance. If everything is > lumped in one table all of your codes would have to take on that new > attribute. This might not seem like a big deal but now your table > encapuslates two different data models. Eventually you end up with > code > that says "if type = this do that". Yes, you could just create a new > table at this point to model just the code that needs the > effective_date column and leave the existing table alone. But, that is > why it''s best practise to start that way. > > However, what you have done in the past WILL work within the > limitations just outlined. > > -Paul > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
What you are talking about is 3NF normalization. Its not always the "best" solution. Upside is that you reduce the chance of invalid data. The downside is that you start doing tons of joins in your queries which slows things down. I don''t even use STI. I use CONSTANTS to define select lists and make sure to comment what the constant is in the migration definition so others can look it up. You can avoid funky data input by performing validation on the data relative to the constant before saving with: validates_inclusion_of :hair_color, :in => HAIR_COLORS I am not a DBA so my way is not necessarily the best way. But it works and I haven''t ever had any data invalidity issues. -- Posted via http://www.ruby-forum.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 -~----------~----~----~----~------~----~------~--~---
Anthony, I have not used STI before but it seems rails provides support in the framework to overcome some of the negatives I mentioned.That is, it provides separate models for each entity as if they each had their own database table and yet stores all of the data in a single table. The main issue with it from my perspective would be that even though you can split it into separate models you cannot designate for instance that columns A and B are only to be used by model A and column C only with model B. However, for something like simple lookup tables it may have it''s place. If your successfully using it in that regard I would not change anything. -Paul --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---