First Normal Form

There is some basic rule for nromalization that might help.

One of Cobb's Rules?

"Every data element must be logically accessible through the use of a combination of its primary key value, table name, and column name."

This goes beyond the 1NF but I think it might help when you get further along.

That has probably been tweaked somewhat but it might help?

I think of getting to the 1NF as having columns that are a description of my data (e.g. for a Football database - Team, Position, Jersey Number) instead of having a column for each Team.

Feel free to bash me if I what I have said is just plain wrong!

Note (03/20/2004): Atomic Data - See Pat Hartman's post

Cosmos75 said:

I think of getting to the 1NF as having columns that are a description of my data (e.g. for a Football database - Team, Position, Jersey Number) instead of having a column for each Team.

Click to expand...

I'd have Team in a table of its own. And I'd have Position in a table of it's own.

I would too, was just trying to give an example that helps me think of what I need to have as columns (as opposed to having a column for each team). I guess I should have also added that each unique data value in a column should only appear once in a table so
Two teams and two positions appear twice in this layout. Since the individual teams and individual positions are not dependant on each other they each get their own table. I am sure this explanation is not very clear but it's how I think about it. I hope someone comes along to help clarify that part.

So, it should be three smaller tables
Each data value is represented only once in a table. The curveball is that there are two players have the same jersey number and I wouldn't split jersey numbers into a seperate table. Even though they have the same numerical value, in the datbase they are "seperate" data. Both players share the same jersey number (can query for people with jersey # = 30) but each have their own number.

Examples of other data tied to a player that could be a column (i.e. field) in tblPlayer - Height, Weight, Date of birth, Date started playing, Date Retired.

Examples of data tied to a player that could have their own table - University Attended , Eye Color, Hair Color. Mostly data that would have a discrete amount of values as opposed to Weight, Height, Data of birth (Imagine a table with all dates from 1930 - present!! - of course you could populate the table as you go along but why go through all that trouble).

Does everyone else see the table columns aligned or does it only seem that way on my screen?

Design Access databases with normal forms and Excel
"This article explains how to use Microsoft Excel to create prototypes of your Access databases and how to make sure your data adheres to a set of rules called normal forms. Normal forms aren't hard to understand, and they're critical to designing useful databases."

Download the Sample Excel File.

I have used a spreadsheet before as a way to explain normalization to someone. Seems to be easier to grasp as most people are familiar with Excel.
1) Have a table of un-normalized data (e.g. what I have for tblSport).
2) Then Auto-Filter on all the columns.
3) If you tried to filter on the Team Column, you would only get one instance of Packers and one of Falcons. That would represent data that should be in a table. (Not exactly a general rule, I know.). Then say you had Packers spelled incorrectly in one row as Pakers? Then your filter options would become Falcons, Packers, and Paker. This is good to show why normalization will help prevent such mistakes.
4) Create a PivotTable. That also helps to show groupings of data that helps further understand how Team relates to Position and so on.

Mile-O-Phile,

Noticed you moved your original post to the FAQ forum. Should I delete my posts here?

No; it's still informative in its own right.

Perhaps I am missing something, but the table structure in the example (if taken as shown) doesn't make much sense. First of all, there is no table to link the data between the tables; meaning there is a table with team names and a table with player names, but nothing that shows what players are on which team.

In addition, two of the tables have only one column, so without any tables to connect them to other data, they're nothing more than lists. There is no reason to break data out into its own table if it doesn't repeat in the same record and does not have any other fields based on it. (Although special cases exist, they go beyond 3rd normal form.)

Is there something you know that I don't? Or is it simply that the example is being logically extended beyond what is explicitly stated in the post?

Kraj said:

Perhaps I am missing something, but the table structure in the example (if taken as shown) doesn't make much sense. First of all, there is no table to link the data between the tables; meaning there is a table with team names and a table with player names, but nothing that shows what players are on which team.

In addition, two of the tables have only one column, so without any tables to connect them to other data, they're nothing more than lists.

Click to expand...

Excellent observation! I only listed the data that would be in the normalized table but I not include the Primary or Foriegn Keys. I only did this as an example of normalized data should look like and did not include discussion on how the tables are related.

So here is the table fields as I would store them in Access
So relationships are now;

• One (tblTeam) to Many (tblPlayer)
• One (tblPosition) to Many (tblPlayer)