Normalization

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Relational Theory » Normalization

145 posts, Page 1 of 15

1 2 3 4 5 »»»

Normalization

Rate Topic

Display Mode

Topic Options

Author

Message

sjsubscribe

Posted Saturday, January 09, 2010 3:39 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589

http://lambda-the-ultimate.org/node/3762

Some of long-standing issues being revisited, but it is interesting to see the debate from functional programming angles.

Post #844962

PaulB-TheOneAndOnly

Posted Wednesday, January 20, 2010 6:41 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 2,982, Visits: 4,399

oh yes! very interesting indeed.

I found the article as interesting as the theory that Grand Cannyon was an alien mining operation is BigGrin

which everybody knows is true, init?

Let me quote "...practitioners have rejected normalization..."
Blink

Really?
Are you kidding? BigGrin

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #850434

sjsubscribe

Posted Wednesday, January 20, 2010 7:48 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589

Certainly the current practice is mixed at best or else no DBMS would allow null values as valid entries in a field. That's what the relational theory purists wants. Rejection refers to that specifically. Hyperbole aside, that's what I got out of the debate anyway.

Post #850960

PaulB-TheOneAndOnly

Posted Thursday, January 21, 2010 5:42 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 2,982, Visits: 4,399

sjsubscribe (1/20/2010)

Certainly the current practice is mixed at best or else no DBMS would allow null values as valid entries in a field. That's what the relational theory purists wants.

I can see no conflict between data normalization and accepting null values in non-key columns - as all rdbms out there allow and enforce.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #851170

sqlvogel

Posted Friday, January 22, 2010 9:16 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 406, Visits: 2,852

PaulB-TheOneAndOnly (1/21/2010)

[quote]I can see no conflict between data normalization and accepting null values in non-key columns - as all rdbms out there allow and enforce.

All the conventional Normal Forms apply exclusively to relations with values, never nulls. In fact normalization predates the invention of SQL and nulls. So you can't say that any table with a null represents a relation in Normal Form.

If you allow an attribute to be null under normalization, you either have to ignore dependencies involving that attribute or you must make an informal (and incorrect) assumption that null can always be treated like a proper value. Formally speaking nulls just can't work with normalization. In particular, there is no clear answer as to what nonloss decomposition or join dependency means for tables that have nulls in them. So no table with a null is truly normalized. Since it is always possible to decompose further and eliminate nulls they are not formally necessary either - you can always create a truly normalized design without them.

David

Post #852130

PaulB-TheOneAndOnly

Posted Friday, January 22, 2010 3:48 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 2,982, Visits: 4,399

David Portas (1/22/2010)...you can always create a truly normalized design without them.

Yes! I'm agreement... but the truth is you can do with or without them Whistling

I know there are people out there investing a lot of time in missinforming good gals and pals but the truth will always prevail - let's go back to the sacred scriptures, in my case - when in doubt - I always go to the old book of Codd.

Firstly... it is not true that Null got invented alongside SQL, Codd described Null. By the way, he got furious when SQL coded a single representation for Null when everybody knows there are two kinds of Null.

Secondly... Codd said "relations have attributes which contain values within a domain" so, lets see what we got here.

a) Null is not a value but the absence of value, domains are made up of values then Null cannot be part of a domain because Null is not a value. As you can see Null gets a free pass.

b) Some attributes in a tuple are not meant to be part of any relationship so people are safe to populate such relationshipless attributes with a Null when needed. Just an example, how about the famous MailAddressSecondLine attribute describing the optional second line of a mail address? you can populate it if needed or you can leave it with no value a.k.a. Null if you have nothing to put in there.

Bottom line is ... Codd invented everything at the same time - including Null - and the model was good but as it happens in all domains of life some people attempts to read what is not there and to stretch the words to extremes beyond what the author intended.

Cheers!

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #852390

sqlvogel

Posted Friday, January 22, 2010 4:33 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 406, Visits: 2,852

PaulB-TheOneAndOnly (1/22/2010)

Bottom line is ... Codd invented everything at the same time - including Null - and the model was good but as it happens in all domains of life some people attempts to read what is not there and to stretch the words to extremes beyond what the author intended.

Codd invented the relational model in 1969. His earliest discussion of nulls was in the "RM/T" paper "Extending the database relational model to capture more meaning" which was 10 years later. Even then he didn't properly attempt to introduce nulls to the relational model until much later, in his RM Version 2 book (1990). If you disagree then please tell me the precise work authored by Codd that you are referring to. You will find no mention of nulls at all in his original papers of 1969 and 1970 so it's not true that nulls originated simultaneously with the relational model itself.

Irrespective of that, the Normal Forms, upto and including 6NF don't leave any room for the possibility of nulls. Codd himself says so: "The concepts and rules of functional dependence, multi-valued dependence, and join dependence were all developed without considering missing db-values" ("Missing Informatlon in Relational Databases", 1986). Consider for example a join dependency JD *({A,B},{A,B,C}). What does this mean if A or B contain nulls? In SQL "nulls don't join" so the join of any projections including null will have fewer rows than you started with. How then can any SQL table with nulls be equal to the join of the projections on its superkeys (in other words 5NF)? It cannot be and therefore no table with nulls can accurately be described as being in 5NF.

David

Post #852402

PaulB-TheOneAndOnly

Posted Friday, January 22, 2010 8:04 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 2,982, Visits: 4,399

wow wow WOW! w00t

No need to declare Jihad on me... we both read from the same book! remember? Laugh

hey ... you missed an opportunity to explain how the old-n-good MailAddressSecondLine issue works for you. Are we in agreement there are attributes that do not call for any relationship and nobody cares if they carry or not a value?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #852445

sqlvogel

Posted Saturday, January 23, 2010 11:31 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 406, Visits: 2,852

Nothing personal. I just wanted to set the record straight, keeping in mind your comments about reading what is not there and stretching the meaning of words beyond their author's intent.

If nobody cares then by the YAGNI principle the attribute should be left out of the database altogether.

I think the problem you are referring to is that it can be a mistake to assume certain properties about addresses which do not hold true in reality. That doesn't have very much to do with a discussion about normalization. It just means that as part of the design process you should determine what dependencies your data is actually going to satisfy.

David

Post #852571

Jeff Moden

Posted Saturday, January 23, 2010 12:34 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

So, when YOU create an address table, do you have a "second address" line or do you normalize it? Enquiring minds want to know. Smile

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #852590

« Prev Topic | Next Topic »

145 posts, Page 1 of 15

1 2 3 4 5 »»»

Permissions