Database normalization questions.

Posted on 2013-06-19
Last Modified: 2016-11-23
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!!
Question by:vsCoder
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 45

Accepted Solution

Kent Olsen earned 250 total points
ID: 39260621
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,

Assisted Solution

thenerdynerd earned 125 total points
ID: 39260623
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.


Assisted Solution

COANetwork earned 125 total points
ID: 39260676
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)

Author Closing Comment

ID: 39261115
Thank you to all!

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question