Database design question

Anon

I am wondering if this makes sense.

I have several tables.

manufacturer
manufacturerid
manufacturername

model
modelid
deviceid
modelname

device
deviceid
devicename

capacity
capacityid
capacityname

speed
speedid
speedname

harddrive
harddriveid
manufacturerid
modelid
capacityid
speedid

My question is does it make sense to create the last table? The harddriveid will be inserted into another table inventory with a prim key call machid. Deviceid will be used to filter manufacturername using javascript in a drop-down menu after the manufacturer has been selected.

Is there a better way to do this?

rrijnders

Personally, I would merge "device" and "model" to one table and eliminate "capacity" and "speed" as seperate tables. They can just be represented in the "harddrive" table with their specific values.

Just my $0.02

-- Rich

sfmnetsys

So you're saying the tables should be look this:

model
modelid
modelname
devicename

harrdrive
manufacturerid
modelid
capacity
speed

sfmnetsys

I would also like to add that the reason I had capacity and speed as separate tables is because I want to use them in other devices such as processors, cd,dvd, etc. Should this make a difference?

rrijnders

Not really.

The speeds and capacities for the different devices don't really overlap. Your hard drive speeds will be 5200/7200/10000 RPM While your CD/DVD speeds will be 8x/12x/24x and CDRW will be composites of 8x\16x. So even if you want to use the tables to load select boxes, you would still need some way to cross reference the appropriate speeds to the corresponding device types.

I think it's better to just store the actual values for speed and capacity. You can always sort/filter by the actual values in the SQL. The extra space of storing the value as opposed to an INT (I am assuming that's how you were storing the ID's) will be more than offset by the CPU you save not having to join the two extra tables every time.