DB design question

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…

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 Q.

503.830.4208
www.jamiequint.com

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.

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