Tip Normalisation (1 Viewer)

[oumahexi]

Hi, I just sent the following to a friend and wondered if it might be helpful to others, it's just a brief overview of Normalisation. I'm sure there are some far more experienced people out there who can expand on this to help novices.

The aim of normalising a database is to both eliminate redundant data, such as data stored in more than one table, and to make sure that dependencies make sense in as much as only related data in a table is stored. This approach not only saves on the amount of space your database uses, but will make sure your data is stored logically.

There are guidelines to making sure your database is normalised. The accepted guidelines are known as Normal Forms and these are numbered one to five, with four and five being rarely used. Remember that these are guidelines, they are not written in stone and from time to time the more confident developer may choose not to follow these guidelines. Written reference to Normal Forms is as follows: 1NF (first normal form) 2NF (second Normal Form), 3NF (third Normal Form), 4NF (fourth Normal Form), 5NF (fifth Normal Form)

1NF sets the basic rules for a well organised database which are to eliminate duplicate columns from the same table and create separate tables for each group of related data and identify each row with a unique identifier known as the primary key.

So, the first rule is not to duplicate data in the same row of a table.

2NF is based on 1NF, in addition it elects to remove subsets of data which apply to multiple rows in a table and put them in separate tables. It then creates relationships between these newly formed tables and the ones they are based on using foreign keys.

3NF is based on 2NF, incorporating the fundamentals of 1NF, a 3NF database removes columns that are not dependent on the primary key.

Remember that normalisation is not a magical button on your software, it is the concept with which you initially design your database.

 

[namliam]

The 3NF is actually the most optimalized form, the 4th and 5th are actualy denormlizing your database a little to ease the use of your database and often used relationships.

One of the two, think its the 5th but not 100% sure, is also called the Boyce-Codd Normal Form (BCNF).
The theory is to keep the PK going down in a relational tree...
I.e.
Customer > Account > Subscription > Product

The product table would have:
Product ID
Subscription ID
Account ID *
Customer ID *

Where the * fields in the 'normal' 3NF would not be there. The idea of this is that the extra storage required for these FKs is minimal and outways the advantages of the saved IO/CPU when running queries over these four (otherwize) required tables.
Now you only need 2.

In the current world where Storage is less and less an issue, while RAM and CPU are now the issue... though actual I/O on a harddrive is still a bottle neck too... BCNF seems to play into this world and is well worth considering on relationships that are often used.

 

[FluffyKitten]

So how do I know if this has been done on my existing database? Is it as easy as looking in each table to see if they have a primary key? Or do I have to make sure they all have relationships and if so how do I know what relationshps they should have? OMG, I didnt know that i new so little!

 

[namliam]

By default I would assume a database is in 3NF, only upon detailed inspection can you say that it is anything else

IF a database is in any NF, for an existing one that is, doesnt matter as most of the time you simply have to live with what you inherited Which (on average) is less than perfect.

We all know why that is, because our customers usually dont allow enough time for "perfection" or even allow enough time to do proper research and you have to live with what you start to design based upon their research which usually ends up beeing only 50% (or what ever percentage) of the actual job to be done.

 

[oumahexi]

By default I would assume a database is in 3NF, only upon detailed inspection can you say that it is anything else

IF a database is in any NF, for an existing one that is, doesnt matter as most of the time you simply have to live with what you inherited Which (on average) is less than perfect.

We all know why that is, because our customers usually dont allow enough time for "perfection" or even allow enough time to do proper research and you have to live with what you start to design based upon their research which usually ends up beeing only 50% (or what ever percentage) of the actual job to be done.

Now, that's probably the most sense I've heard spoken all day!

 

[gemma-the-husky]

in practice, you often find that systems develop in use with

"can we do this"
"can we add this"

if the normalisation/data analysis was sone well in the first place, it is generally easy to add the extra info, while maintaining the data integrity. If the look and feel is good also, user acceptance is much better, and the users themselves make useful suggestions instead of complaining about soluions forced on them.

If it wasnt well normalised to start with, then you often have difficulty finding a good way of incorporating the new fields.