Hi, I''m developing a database and web app to store hardware config info for around 80-100 Macs. Wondering what the best way to design the DB for Rails would be. There are only a finite number of configs, and the specifications for the hardware wouldn''t need to change too often. I''d like the user creating a profile to choose each hardware spec from a drop down select box which are populated from tables. The profile for each system would be stored in a separate table using foreign keys to reference the hardware spec tables Following db normalizaion rules, I would need to separate out every hardware attribute to avoid having duplicate entries (i.e., a table for cpu_models, cpu_speeds, ram_sizes, ram_speeds, etc). This would result in probably around 30 tables with just one column each (aside from id), and seems wildly inefficient, especially if I have to construct 30 different model definitions inside Rails. The other option I''m looking at is to make a table for each hardware component and its relevant attributes such as: Table CPU (id, number, model, speed) This would result in a large number of entries, however, one for each permutation of hardware (changing one attribute wouldn''t necessarily affect the others, leaving columns either duplicate or NULL). It also becomes a problem when I just want to throw up scaffolding to starting entering data, I only need to see the value of any attribute once, not for every duplicate record. Any thoughts on how I should proceed? I know this isn''t a Rails specific question, but considering how nicely Rails handles DBs, I''d really like to avoid making this hard... -- 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 -~----------~----~----~----~------~----~------~--~---
The way that would make sense to me is to have configurations (i.e. everything that has to do with the computer: cpu_models, cpu_speeds, ram_sizes, ram_speeds, etc) all reside in one table, say a "configuration" model. You could then also have another model called "computer" (this could contain data types such as serial number, or whatever else is unique to that computer) With this setup you could then use a has_one relationship in Rails (computer has_one configuration) to associate computers with configurations. "It also becomes a problem when I just want to throw up scaffolding to starting entering data, I only need to see the value of any attribute once, not for every duplicate record." Scaffolding is not meant as an end all to do everything for you, but if you organize data the way I''ve suggested above you should not have to do much work to make it display attribute values the way you want. Good Luck! Best, Jamie Quint 503.830.4208 www.jamiequint.com On 9/21/06, Bryan M <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > > Hi, > > I''m developing a database and web app to store hardware config info for > around 80-100 Macs. Wondering what the best way to design the DB for > Rails would be. There are only a finite number of configs, and the > specifications for the hardware wouldn''t need to change too often. I''d > like the user creating a profile to choose each hardware spec from a > drop down select box which are populated from tables. > > The profile for each system would be stored in a separate table using > foreign keys to reference the hardware spec tables > > Following db normalizaion rules, I would need to separate out every > hardware attribute to avoid having duplicate entries (i.e., a table for > cpu_models, cpu_speeds, ram_sizes, ram_speeds, etc). This would result > in probably around 30 tables with just one column each (aside from id), > and seems wildly inefficient, especially if I have to construct 30 > different model definitions inside Rails. > > > The other option I''m looking at is to make a table for each hardware > component and its relevant attributes such as: > > Table CPU (id, number, model, speed) > > This would result in a large number of entries, however, one for each > permutation of hardware (changing one attribute wouldn''t necessarily > affect the others, leaving columns either duplicate or NULL). It also > becomes a problem when I just want to throw up scaffolding to starting > entering data, I only need to see the value of any attribute once, not > for every duplicate record. > > Any thoughts on how I should proceed? I know this isn''t a Rails specific > question, but considering how nicely Rails handles DBs, I''d really like > to avoid making this hard... > > -- > 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 -~----------~----~----~----~------~----~------~--~---
I would make it more dynamic, have two tables model and model_properties: model --------- id manufacture model_number model_properties ------------------------ id model_id property_name property_value You would have a 1-M relationship between model and model_properties. This way you can have as many properties as you want per model. scott. On Sep 21, 2006, at 4:46 PM, Bryan M wrote:> > Hi, > > I''m developing a database and web app to store hardware config info > for > around 80-100 Macs. Wondering what the best way to design the DB for > Rails would be. There are only a finite number of configs, and the > specifications for the hardware wouldn''t need to change too often. I''d > like the user creating a profile to choose each hardware spec from a > drop down select box which are populated from tables. > > The profile for each system would be stored in a separate table using > foreign keys to reference the hardware spec tables > > Following db normalizaion rules, I would need to separate out every > hardware attribute to avoid having duplicate entries (i.e., a table > for > cpu_models, cpu_speeds, ram_sizes, ram_speeds, etc). This would result > in probably around 30 tables with just one column each (aside from > id), > and seems wildly inefficient, especially if I have to construct 30 > different model definitions inside Rails. > > > The other option I''m looking at is to make a table for each hardware > component and its relevant attributes such as: > > Table CPU (id, number, model, speed) > > This would result in a large number of entries, however, one for each > permutation of hardware (changing one attribute wouldn''t necessarily > affect the others, leaving columns either duplicate or NULL). It also > becomes a problem when I just want to throw up scaffolding to starting > entering data, I only need to see the value of any attribute once, not > for every duplicate record. > > Any thoughts on how I should proceed? I know this isn''t a Rails > specific > question, but considering how nicely Rails handles DBs, I''d really > like > to avoid making this hard... > > -- > 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 -~----------~----~----~----~------~----~------~--~---
Thanks, both your suggestions were good. It had never occured to me put in a generic "property_name" field, and is much easier than trying to store each one as its own field. So I''m looking at something like this table "ident" -------------------- id serial_number employee department table "hardware" -------------------- id component model table "properties" --------------------- id hardware_id property_name property_value Honestly, I don''t feel the need to specify individual models in the hardware table, simply because things like RAM are non-model specific, and on Macs, the same processor "model" (like the PPC G5) often has a variable number of speeds. However, creating specific model configs makes setting up my profiles table easier: table "profiles" ------------------------- id ident_id cpu_id mobo_id ram_id hdd_id vid_card_id ...etc Without setting up model to property relationships, it seems I would be stuck making a foreign key for every property in my "profiles" table. So this seems like the most elegant solution to me so far. Thanks for the advice. Bryan -- 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 -~----------~----~----~----~------~----~------~--~---