What types of normalisation are there?

[Guus2005]

Firstly, i normalised 90% of the databases i build. I always normalise up to the BCNF.
In the past i came accross a hierarchical database which consists of 1 main table with the fields Parent, Child, Value. It is extremely dynamic. You can have as many levels until you reach the record limit or the database 2GB limit whichever comes first.

I believe any database could be setup to a single table like this. But ofcourse it comes with a prize. It reduces speed considerably when you have to recursively read all levels to get somewhere.

Normalising to a single table has a name which eludes me for the moment and having that name, what is "normal" normalisation called and are there more types and when are they used?

Thanks!

 

[Rabbie]

Firstly, i normalised 90% of the databases i build. I always normalise up to the BCNF.
In the past i came accross a hierarchical database which consists of 1 main table with the fields Parent, Child, Value. It is extremely dynamic. You can have as many levels until you reach the record limit or the database 2GB limit whichever comes first.

I believe any database could be setup to a single table like this. But of course it comes with a prize. It reduces speed considerably when you have to recursively read all levels to get somewhere.

Normalising to a single table has a name which eludes me for the moment and having that name, what is "normal" normalisation called and are there more types and when are they used?

Thanks!

I may be being pedantic but I feel it would be an exceptional data base that could be both normalised and held in a single table. As soon as any entry in the DB referred to more than one item then it would be denormalised. To me a PArent Child relationship allows the possibility of 1 to Many and as soon as this occurs your data is denormalised. Perhaps I am missing something in your post so more detail would be helpful - preferably with an actual example to illustrate what is going on.

 

[Guus2005]

The one table also holds the definition of other tables. This means that it is extremely flexible. You can add fields on the fly. You must add fields to store types other than text.
Another type of normalisation is used when buidling datawarehouses.

Don't have time to develop an example. Don't even have time to write complete sentences.

I'm curious of its name and if there are other types ans when can you use them.

HTH

 

[namliam]

As far as I know there is only one form of normalisation, which is the one you described.. 1,2,3 and 4 (BCNF).. I think there is a 5 as well in some theories...

Normalisation is something you do for Relational databases, Datawarehouses are not necesaraly Relational. They are not aimed for that anyway they are aimed at reporting ASAP, for which many a times a lot of "pre-fab" reports are allready pre-aggrigated which would be many many many records in the original db...

 

[georgedwilkinson]

OLTP databases should be normalized to the 5th normal form (BCNF), as Mr. Codd intended when this whole thing began (actually, the standard for 5th NF states this almost verbatim). Usually, if a database is normalized to the 3rd NF, it also is normalized to 5th NF. Also, since DBMS' are designed using the Codd model, most of them actually perform much better when everything is properly normalized, with everything in is nice little atomic table.

If only 90% of your db is normalized, the database is not normalized, though some of the tables could be said to be normalized.

There are various schemas for data warehouses. Two of the most popular are the star schema and the snowflake schema. They are relational in nature but are optimized for getting large amounts of data out quickly and accurately, not for managing a large volume of transactions.

Some of the reasons even hardened data modelers don't use a single table for OLTP databases:
1. It doesn't accurately model what is happening in the real world. And data modelers seem to have an aversion for anything in a database that doesn't look like the real world.
2. The nice people at Oracle, Sybase, Microsoft, Informix, etc. already did all that work for us. They even gave us a slick (and already incredibly well-defined and documented) language for changing data layouts on the fly called DDL, and a way to optimize our data manipulation routines.
3. It is not optimized and not optimizable (try putting an index that makes sense on a single table).
4. It is nearly impossible for even the smartest developer to figure out what is going on. I'd love to see the query for getting a PO line item for the items a user is responsible for ordering when there is only one table.
5. User interface design is difficult/impossible.
6. It is a major evolutionary step backwards.

Times even intermediate data modelers will use a single table instead of the multiple tables a software developer would normally use:
1. Employee, customer, vendor, division, store, any type of person, any type of organization: these are all a single entity called "Party" with sub-entities "Person" and "Organization". This could translate into 1 table, 3 tables, or n tables during implementation.
2. Invoice, Journal entry, credit memo, debit memo, etc.: These are all a single entity. Just like with party, they may be broken up into 1 or more tables, depending on what the system is doing.
etc.

Good data modeling is closely akin to Object Oriented modeling.

If you want a sickening amount of information about what should and should not be done while modeling a database, look up data modeling on Google. These guys will fight over just about anything you could imagine when discussing a data model...with each other and especially, with us. I had to learn all this crap 15 years ago to keep them from harassing me for my "stupid" models.

 

[aceto]

What your describing sounds like EAV (entity-attribute-value) to me and
it is not based on the rules of normalization. It's something that many
people think they have discovered at one time or another.

 

[georgedwilkinson]

What your describing sounds like EAV (entity-attribute-value) to me and
it is not based on the rules of normalization. It's something that many
people think they have discovered at one time or another.

I wish I were clever enough to discover such things without help. Fortunately, I was able to read all these rules and put them into practice in the real world. The rules on normalization are well documented and I'm happy to report that I discovered them in a book and was able to put the information to use.

As far as the terminology used for SDLC purposes, I'm going straight back to my Oracle Designer training and subsequent experience. Other methodologies use different terminology, though the principles are sound, regardless of the words used to express them.