Normalization

[nummer31]

new to SQL and PHP

so, i've been recommended to read normalization steps

but the book just makes it more harder for me to design a DB in practical

so, all the pros say that they don't particularly follow normalization

but it's like ABC to the them

so, how do they think?

can smn outline simple rules of the Normalization steps which will be helpful in practical designing?

[ignace]

I think their are 2 main streams in the normalization process:

 

1) The Codd method

 

Starts with NF0 which means all data in one table. Afterwards you apply the laws of Codd to become a NF3 or possibly an NF5 (NF3 is sufficient in most cases). Also note that in some circumstances denormalization will be applied if performance may benefit from it.

 

Example: http://en.wikipedia.org/wiki/Database_normalization#Objectives_of_normalization

 

2) Don't the know the exact name of the second method but is commonly referred to as an ERD

 

You start out by creating entities (little boxes with names in it, singular) and you try to find their relationships

 

 

The diamond's in the picture represent relations, the circles are fields, the squares are table names.

[nummer31]

thank u ignace

so, i'll simplfy the steps below:

1. Draw and ERD and take out the fields and tables

2. Take all the fields in one table

3. Choose a primary key and put redundant fields in another table and so on

4. Choose primary keys for other tables and corresponding foreign keys

5. Look for dependencies and eliminate them

but i have a question:

how do i break many to many relationships??? 

[ignace]

No they are 2 different methods either you use the ERD or you use the Codd method don't mix them. And the Codd method is actually a bit harder then your 3-5 steps, see Objectives of Normalization.

 

how do i break many to many relationships???

 

You create a bridge-table that has a foreign key for both tables primary keys.

 

 

[Daniel0]

2) Don't the know the exact name of the second method but is commonly referred to as an ERD

 

You are not guaranteed any normalization form just because you've made an E/R-diagram.

[ignace]

2) Don't the know the exact name of the second method but is commonly referred to as an ERD

 

You are not guaranteed any normalization form just because you've made an E/R-diagram.

 

How do you mean:

 

1) The person creating the ERD has a limited knowledge of database design and can therefor not create a normalized ERD?

2) or, the person creating the ERD has sufficient knowledge in database design, but due to not using Codd's method ends up with an ERD of which he only thinks is normalized?

 

If 2, then yeah I have been thinking about that, it always turned out right (sort of). They taught us in college it this way and told us that we should favor it over Codd's method. Like most of the times they give no reason as to why, just that we must. Most students (like myself back then) are naive and ignorant, and asking for motive is (de facto) verboten.

 

2 years later and I haven't looked it back up, care to share some insights?

[Daniel0]

2) Don't the know the exact name of the second method but is commonly referred to as an ERD

 

You are not guaranteed any normalization form just because you've made an E/R-diagram.

 

How do you mean:

 

1) The person creating the ERD has a limited knowledge of database design and can therefor not create a normalized ERD?

2) or, the person creating the ERD has sufficient knowledge in database design, but due to not using Codd's method ends up with an ERD of which he only thinks is normalized?

 

Neither. I just mean that because you created a diagram of a schema, you aren't ensured that it's normalized. Consider the two E/R-diagrams I've attached (bold outlined attributes are primary keys, I couldn't figure out how to make things underlined using graphviz).

 

In foo.png, we have two functional dependencies on Movies:

Title,Year -> Genre,Director

Director -> DirectorAddress

 

This violates BCNF (Boyce-Codd Normal Form) because it requires that all left-side terms of non-trivial functional dependencies must be a superkey in that relation. However, Director is not a superkey in the Movies relation. Title,Year is a superkey though, because functional dependencies are transitive.

 

The bar.png diagram shows a normalized version.

 

[attachment deleted by admin]

[ignace]

What do you mean by

 

Title,Year -> Genre,Director

Director -> DirectorAddress

 

I understand that bar.png is better this is how I would create it.

[Daniel0]

http://en.wikipedia.org/wiki/Functional_dependency