to normalize or not to normalize table?

  • Thread starter Thread starter Mitchell_Collen via AccessMonster.com
  • Start date Start date
M

Mitchell_Collen via AccessMonster.com

What are all the benefits of normalizing tables in a database? I have heard
someone say that it only reduces space nothing more. Please advise.
Misty
 
Much more. While reducing size is a consideration, properly normalized data
promotes data integrity. It will remove various anomalies that can occur in a
big "one table" approach which is very much like using a spreadsheet.

I highly recommend reading "Database Design for Mere Mortals" by Hernandez.

For a quick study:
http://en.wikipedia.org/wiki/Database_normalization
 
Here's a practical example. Let's say I have 22,000 employees in my company
and I am one of the corporate trainers (both true).

I need to keep track of which student went to which class.

If I created a table like this:

Student First Name
Student Last Name
Class Name

Then I could have multiple entries for any person. Let's say Sally Smith
decides to get married and wants her name changed to Jones. She has attended
35 classes since she's been here.

Do I want 35 places I have to change the data or ONE?

THAT is data integrity and that is why normalization is so important.

Lauri S.
 
I'll offer another reason...

Microsoft Access offers a variety of features and functions, but they are
optimized for relationally-oriented data (i.e., normalized). While you can
feed Access 'sheet data, you (and Access) will have to work much harder than
if you give it well-normalized data.

In keeping with the examples notion, having 12 columns reserved for "Jan.",
"Feb.", "Mar.", ... "Dec." is how you'd handle a series of amounts in a
spreadsheet. But to do even the simplest math on these amounts in an Access
table, you have to spell out each/every column to use.

Or you could store [Amount] and [DateOfAmount] (?!just two columns) and make
your table "narrow and deep" instead of "wide and shallow". The former uses
Access' strengths, the latter is better off in a spreadsheet...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I'll offer another reason...

Microsoft Access offers a variety of features and functions, but they are
optimized for relationally-oriented data (i.e., normalized). While you can
feed Access 'sheet data, you (and Access) will have to work much harder than
if you give it well-normalized data.
Click to expand...

How about an analogy? If you're building a house, even a very
talented carpenter cannot build a good house if the foundation is bad.

The database structure is the foundation. Your application is the
rest of the house. You are the carpenter.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
To take Jeff's example one step further, lets say you are keeping track of
sales by store by month. Now in your non-normal table structure you may have
columns for Jan 06, Feb 06, Mar 06, Apr 06, ... Dec 07.

If you want to compare the total sales for 06 and 07, you have to sum across
12 columns for each year, and since the years and months are hard coded into
the field names, if you decide you want to compare 06 and 08, you have to
write an entirely new query.

If your data is normalized:

Store#, SalesDate, Amount

You can write a query that is based on values in a form to do this for you
without any additional work:

SELECT [Store#],
Year(SalesDate) as SalesYear,
SUM(Amount)
FROM yourTable
WHERE Year(SalesDate) = Forms!yourForm.cbo_FirstYear
OR Year(SalesDate) = Forms!yourForm.cbo_SecondYear
GROUP BY [Store#], Year(SalesDate)

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Jeff Boyce said:
I'll offer another reason...

Microsoft Access offers a variety of features and functions, but they are
optimized for relationally-oriented data (i.e., normalized). While you can
feed Access 'sheet data, you (and Access) will have to work much harder than
if you give it well-normalized data.

In keeping with the examples notion, having 12 columns reserved for "Jan.",
"Feb.", "Mar.", ... "Dec." is how you'd handle a series of amounts in a
spreadsheet. But to do even the simplest math on these amounts in an Access
table, you have to spell out each/every column to use.

Or you could store [Amount] and [DateOfAmount] (?!just two columns) and make
your table "narrow and deep" instead of "wide and shallow". The former uses
Access' strengths, the latter is better off in a spreadsheet...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Back
Top