Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Normalization Expand / Collapse
Author
Message
Posted Saturday, January 9, 2010 3:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Wednesday, January 20, 2010 6:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
oh yes! very interesting indeed.

I found the article as interesting as the theory that Grand Cannyon was an alien mining operation is which everybody knows is true, init?

Let me quote "...practitioners have rejected normalization..."
Really?
Are you kidding?


_____________________________________
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
Posted Wednesday, January 20, 2010 7:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Thursday, January 21, 2010 5:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Friday, January 22, 2010 9:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 448, Visits: 3,349
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
Posted Friday, January 22, 2010 3:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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

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
Posted Friday, January 22, 2010 4:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 448, Visits: 3,349
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
Posted Friday, January 22, 2010 8:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
wow wow WOW! No need to declare Jihad on me... we both read from the same book! remember?

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
Posted Saturday, January 23, 2010 11:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 448, Visits: 3,349
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.

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?


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
Posted Saturday, January 23, 2010 12:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 37,065, Visits: 31,628
So, when YOU create an address table, do you have a "second address" line or do you normalize it? Enquiring minds want to know.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #852590
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse