Database normalization questions.

Hello Experts,

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.

Table #1

Table Name:

Column Names:
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?

Thank you!!
Who is Participating?
Kent OlsenData Warehouse Architect / DBACommented:
Hi Coder,

Those are some very good questions.

1.  This is a great place to normalize.  It keeps the database clean by not allowing multiple things to mean the same thing.  Using just the three type that you suggest, it gets tough to interpret the true meaning when the type is stored as "Laptop".  Moving the text (name) to another table solves that.  And if you build a web page or application to maintain this, pulling the names from the AssetTypes table is cleaner than pulling the DISTINCT names from the AssetType column in Table1.

2.  Another good place to normalize, especially if the manufacturer's name will appear in a drop down for user selection.

3.  This isn't so clear, and would depend on your usage.  The purist in me says to normalize this.  Again, if you're populating a drop down, normalize it.  When a new model becomes available, just inserting a row in the table makes it available to the drop down.  It also prevents typing errors from storing the wrong model number.

4.  You could easily find a lot of data that you eventually want to store.  Assuming that this is inventory control, you could want the invoice number when it was bought, or the date that it was bought, the date that it went into service, etc.  Model and Serial number would be the primary key.  Actually, I'd probably define a composite index over Model and Serial Number and use an IDENTITY column for the primary key.

I believe that you're on the right track!

Good Luck,
Hey there,

I think you're on the right track.  Sounds like good planning.  What I tend to usually do is think how many records I could see in the future, how other information maybe used elsewhere in my db.

1.Because you're ever going to have a handful of devices I reckon you could limit to a list rather than a separate table.  Again unless you are planning on using these Devices list elsewhere.

2. With Manufacturers even though at the moment you have 3 main ones you could put this on a separate table and all sorts of information, like company contact, company email etc.  So you could use a table as suggested and will give you that option going forward.  This was you can link them elsewhere.

3.Model Number is a tricky one, if you go down the route of into a table I would maybe move assetType to a separate table, then linked to model numbers which in turn linked to serials description and asset tag.

Saying all the above this is how I would approach it:  The '-->' is the link FK and PKs that I would use to link the tables in this hierarchy.

Manufacturers (Companies) --> AssetType --> ModelNumbers --> Serials (Id, SerialNumber, AssetTagNumber)

Hope that helps.

You are headed in the right direction.  All your assumptions so far are correct, but to quote a popular phrase, the answer to any SQL server question is "It depends..."
Traditionally, the rule of thumb level of normalization for RDBMS is "3rd normal form", which basically means that your object tree should never be more than 2 FKs deep.  In your case, if you use an asset type table with an FK to it from your Asset table, the AssetType table itself may have an FK to an even lower table in the hierarchy, but that table cannot reference anything else further down.  Hope I am explaining this clearly enough.
However, since you are not in a perfect world, an OLTP database may need some artful denormalization - if you have queries that you run frequently that require information from 15 tables in 3rdNF, you may want to denormalize and keep the number of tables to a minimum to speed up the queries.
Normalization is good for flexibility (design changes, business logic changes) and for OLAP, but the tradeoff is speed - the more normalized your DB is, the slower it will perform (relatively slower)
vsCoderAuthor Commented:
Thank you to all!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.