Hello everybody. I have a question about designing the database to be used with rails. I have a table with items: Items: - ID - Name - Number - Color_ID also each item can be of different sizes(70,75,80,85,90,100) and I have different quantity f each size. Something like this: ID/Name/Number/Color_ID/Size_ID/Quantity 1 /Bra /3751 /1 /70 / 3 / 2 /Bra /3751 /1 /75 /2 /
So the question: what is the best way to design this database, I am new to this so any help will be greatly appreciated.
The way I see it:
Name, Number, and Color_ID are redundant fields. 1.1 I think you should separate this fields into their own table.
Create a new table with Size_ID/Quantity as fields.
Put a table for storing Color. 3.1 Color_ID will be your primary key.
The suggestions from above would help u :) Always remember, "Normalisation" is the one of the keys in database design. Think always in terms of huge data this would help you figure out what is repeated? What is redundant? Not just that, it is always easy(I mean in very less time!) to pull out data if it is key dependent/matching, not the string dependant/matching!
cheers! Sumanth Krishna
First normal form 1.1 Remove the fields that have multiple values in it(for a single record) and turn it into a separate table.
Second normal form 2.1 Must be in first normal form. 2.2 Remove records that have repeating field values and turn them into a separate table.
Third normal form 3.1 Must be in second normal form. 3.2 The tables an in it atomic form. Which means all of the fields must depend on the primary key alone.
Hope that helps.