Solved

Database normalization questions.

Posted on 2013-06-19
4
666 Views
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:
Assets

Column Names:
ID (primary key)
AssetType
Description
Manufacturer
ModelNumber
SerialNumber
AssetTagNumber


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).

Questions:

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!!
0
Comment
Question by:vsCoder
4 Comments
 
LVL 45

Accepted Solution

by:
Kdo 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,
Kent
0
 
LVL 1

Assisted Solution

by:thenerdynerd
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.

Ed
0
 
LVL 9

Assisted Solution

by:COANetwork
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)
0
 
LVL 1

Author Closing Comment

by:vsCoder
ID: 39261115
Thank you to all!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now