Question Third Normal Form?

[phiednate]

Wazz mentioned normalization in my previous post:

http://www.access-programmers.co.uk/forums/showthread.php?t=176093

I read up but for a novice, some of it doesnt seem to be practical. First and second normal form seem to make sense and I have been following that without even knowing. From what I read, third normal form just doesnt seem to practical.

For instance; the database I have started has a table of the contract resource advisors. In that table I have information such as first name, last name, phone, etc. One of the elements is the squadron they are from. If I understand this right, third normal form states that I shouldn't put fields in tables that dont depend on the key. Based on this, I should have a seperate table for squadron. If I were to do this, the only data that would be in this table would be the table key, customer table key and the squadron. I guess what I dont understand is why this is necessary. In the same regard, based on normalization, wouldn't you need to have a seperate table for items such as zip code or phone number (some people on base share phone numbers).

I have a feelign I understanding this wrong since I can see this being a mess of table and relationships. Can anyone clarify this for me?

 

[wazz]

... In the same regard, based on normalization, wouldn't you need to have a seperate table for items such as zip code or phone number (some people on base share phone numbers).

I have a feelign I understanding this wrong since I can see this being a mess of table and relationships. Can anyone clarify this for me?

sounds like you get it.
you can take normalization a long way. it all depends on your situation. if you do find you would be entering a piece of data more than a couple of times, or several times, you're probably heading for another table. doing that can really save yourself when someone makes a typo or simply enters data in the wrong place. it "forces" entry of the proper data in the proper place and helps when searching for information that uses that data, i.e. it finds all of it.

a lot has been written about this on these forums. be sure to search for normalization. if you go to advanced search, you can also look for posts by a certain user; add the_doc_man as the user. he da' man.

 

[Galaxiom]

My understanding is that the Third Normal form would be satisfied with a Squadron field in the Contract Resource Advisor table provided no further information relating to the squadron was also included in that table. If Squadron is a property of the Advisor then it is fine to enter it directly.

There is nothing in NF3 that says every Advisor must have a unique Squadron as you seem to be alluding to in the comparison with phone number and postcode.

However in practice I would probably have a Squadron table and refer to its key from the Advisor table unless Squadron is a simple code already. Also, further information about the Squadron might be useful in this table, such as Location or Contact which definitely would break NF3 if included in the Advisor table.

However I don't understand the full structure such as where the Customer field comes into the proposed Squadron table as it had not been previously mentioned in any context.

 

[phiednate]

After reading the information that Wazz posted to me in another post I found the following little snippet:

EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate all possible interfield dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records. In theory, normalization is worth pursuing. However, many small tables may degrade performance or exceed open file and memory capacities.

Pretty much was exactly what I was looking for. It seems like my squadron field would fit the bill of an exception since there really isn’t any other information that would fall under it. Ideally it will be entered into the database in a form with a drop down listing since there really aren’t any changes to the names or squadrons that are on the base. The way I had originally read the articles was that it was pretty much access law so I wanted to get it right the first time. Looks like I am still on track. Thanks everyone!

 

[DCrake]

I have come across examples of people creating tables with just Male & Female or Yes and No or True and False. As you say these lists don't change so why create them in the first instance combos can be populated with items without the need of a recordsource.

What you need to think about is elasticity of the data. Once created how often is data in table going to be added/edited/deleted? alot? a little? never?

Likewise if your database back end is large there is no reason why you can't seperate these tables into different back ends all conected to the front end. A perfect example I have is that I have a table with 300k+ names and addresses that is only updated one a quarter. But the actual data in the backend has little growth. to include all the tables in one back end is very heavy, however, I seperated the MPI into its own mdb and the performance increase was great as there was less network traffic when the tables were not being accessed.

David

 

[Galaxiom]

If you have a Customers table and you want to eliminate all possible interfield dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records.

http://en.wikipedia.org/wiki/Third_normal_form
Read this description of 3NF at Wikipedia and you will find it does not match with this statement except in one case.

Zipcode and city have a dependency. A city will have a persistent zipcode and to satisfy 3NF the zipcode should be held in a table with city and zipcode. The derived zipcode should not be stored in the customer record.

However the mere duplication of a value in a field does not make it in breach of NF3 or require (in theory) that the values be held in a separate table.

If this were so then street should be in a table because more than one customer could live in the same street and NF3 would be so often excepted it would not be of any use.

3NF just says:
The relation R (table) is in second normal form (2NF)
Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every key of R.

A customer's address is directly dependent on the customer, not on another value. Multiple customers can live at the same address and be separately recorded without breaching 3NF.

However if the customer lived in a building that was recorded in another table then including both the building name and the address of that building in the customer record would breach 3NF.