Database normalization questions.
Posted on 2013-06-19
Although I've written code that pulls data from databases, I have not had to create a database from scratch and go through all of the normalization process. I'm creating a new database from scratch and want to make sure that I understand the normalization process. I've listed my scenario and thoughts below. I'm wanting to know if what I'm thinking is correct - I've tried to keep the example simple just to relay my thought process/understanding of normalization.
Any comments/advice you can provide will be helpful.
ID (primary key)
I'll have other tables in the db, but I would like some confirmation that my thought process is starting out correctly at least for this first table. This table will contain the individual asset listing (dealing with computers only for simplicity).
1. I'd like to restrict my AssetType to either Laptop PC, Desktop PC, Laptop MacBook, Desktop Mac. Given this, is it better to have a separate table containing these fixed asset types versus repeating the same text within this Assets table (and use a Foreign Key (FK) in this table)?
2. We typically only buy from three manufacturers - Dell HP, and Apple. As in question 1 above is it better to have a separate table containing the name of all of our computer manufacturers and use an FK in this table?
3. We tend to purchase same ModelNumber over and over again until it is replaced by a newer model. Is it better to place all model numbers in a separate table and have an FK in this table?
4. The only unique data I see for this table is the ID, SerialNumber, and AssetTagNumber for each computer. Therefore, all of these columns will reside in this table with all other column data being stored in a separate specific table and using relationships.
Am I totally off base here? OR headed down the right path?