November 8, 2011 at 11:43 pm
Steve Jones - SSC Editor (11/8/2011)
I'm sure you'll pass me. I am down to about 150 posts a month, too busy with other stuff and most questions are being handled.
Was this you as well?
If it was, then your point total would have been 10,000 higher.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 9, 2011 at 5:19 am
L' Eomot Inversé (11/8/2011)
Roy Ernest (11/8/2011)
Hey everyone, I am hijacking the thread with an "Off Topic"I am planning on a presentation for my user group "DBA MythBusters" (Courtesy Paul Randal) His idea is brilliant. It is a fun filled session. I would like to do one like that for our user group. If anybody have suggestions on what Myth to bust please, please let me know. The audience is 100 to 200 level.
Here are a couple of normalisation myths; probably more verbose thanm I should be, and it's a bit of a rant.
To me the worst myth is "The database should have nothing to do with business logic", when the whole point of designing schemata with (primary and foreign and unique) key constraints and domain (column type, non-nullability, and check) constraints and doing normalisation is to ensure that the very structure of the schemata and their constraints enforces business rules, to make it easier to write code which will not result in data that is invalid according to business rules, and to make it very difficult (if not impossible) to write code that will allow data in the database to violate those business rules.
The next bad normalisation myth is that normalisation always increases the amount of storage required - a database in 3NF will require more storage than the same data expressed in an unnormalised (not even 2NF) way; in fact normalisation almost always reduces the storage required - and each step from 1NF to 5NF reduces datbase size; denormalisation is sometimes needed to gain performance in an effectively read-only database (typical BI or DWH usage) but that is not a size issue; certain 3NF schemata have no 4NF equivalents which enforce all the business rules enforced in the 3NF version so it is sometimes necessary to keep some tables below 4NF in order to preserve enforcement of required referential integrity, but this is extremely rare and even when it happens doesn't usually cause much size increase.
The third myth is that normalisation is difficult to understand, intellectually challenging. It isn't - it's just that it's pretty rare that anyone describes it in everyday terms instead of in academic jargon.
A fourth normalisation myth that is very damaging is propagated by the happy band of anti-null fundamentalists. They teach that all normalisation is rendered impossible as soon as you have a nullable column in your table. This is extremely damaging because naive people believe them, and although they have to work with rdbms products that allow nulls and soon discover that nulls are essential for some of their work they decide not to attempt normalisation, because they've been told that for their null-permitting systems it is impossible. This leads them to produce incredibly bad schemata that leave the database wide open to application errors that ought to have been prevented by key constraints. Disliking nulls is very sensible (I hate the things myself, except when I really need them), pretending they are not needed is stupid, and claiming they make normalisation impossible is irresponsible vandalism.
*wild applause*
-Ki
November 9, 2011 at 6:01 am
Kiara (11/9/2011)
L' Eomot Inversé (11/8/2011)
Roy Ernest (11/8/2011)
Hey everyone, I am hijacking the thread with an "Off Topic"I am planning on a presentation for my user group "DBA MythBusters" (Courtesy Paul Randal) His idea is brilliant. It is a fun filled session. I would like to do one like that for our user group. If anybody have suggestions on what Myth to bust please, please let me know. The audience is 100 to 200 level.
Here are a couple of normalisation myths; probably more verbose thanm I should be, and it's a bit of a rant.
To me the worst myth is "The database should have nothing to do with business logic", when the whole point of designing schemata with (primary and foreign and unique) key constraints and domain (column type, non-nullability, and check) constraints and doing normalisation is to ensure that the very structure of the schemata and their constraints enforces business rules, to make it easier to write code which will not result in data that is invalid according to business rules, and to make it very difficult (if not impossible) to write code that will allow data in the database to violate those business rules.
The next bad normalisation myth is that normalisation always increases the amount of storage required - a database in 3NF will require more storage than the same data expressed in an unnormalised (not even 2NF) way; in fact normalisation almost always reduces the storage required - and each step from 1NF to 5NF reduces datbase size; denormalisation is sometimes needed to gain performance in an effectively read-only database (typical BI or DWH usage) but that is not a size issue; certain 3NF schemata have no 4NF equivalents which enforce all the business rules enforced in the 3NF version so it is sometimes necessary to keep some tables below 4NF in order to preserve enforcement of required referential integrity, but this is extremely rare and even when it happens doesn't usually cause much size increase.
The third myth is that normalisation is difficult to understand, intellectually challenging. It isn't - it's just that it's pretty rare that anyone describes it in everyday terms instead of in academic jargon.
A fourth normalisation myth that is very damaging is propagated by the happy band of anti-null fundamentalists. They teach that all normalisation is rendered impossible as soon as you have a nullable column in your table. This is extremely damaging because naive people believe them, and although they have to work with rdbms products that allow nulls and soon discover that nulls are essential for some of their work they decide not to attempt normalisation, because they've been told that for their null-permitting systems it is impossible. This leads them to produce incredibly bad schemata that leave the database wide open to application errors that ought to have been prevented by key constraints. Disliking nulls is very sensible (I hate the things myself, except when I really need them), pretending they are not needed is stupid, and claiming they make normalisation impossible is irresponsible vandalism.
*wild applause*
+1
November 9, 2011 at 6:22 am
GilaMonster (11/8/2011)
How can BCNF be sensibly discussed if the earlier normal forms aren't known?
As far as I can tell, it can't. Obviously it can be discussed, but not , in my opinion, sensibly.
Also, omitting the earlier forms means that you never see the serious conceptual break between 3NF and 4NF (more strictly between EKNF and BCNF) where the representation principle goes out of the window, so that you don't realise that it can be very important to retain some tables at a lower normalisation level than BCNF in order to prevent the normalisation process destroying the capability for key constraints to enforce some essential business rules, a capability that exists all the way up to EKNF (and for most schemas, at all higher levels too - but the exceptions can ruin a project if it doesn't watch out for them).
If Stanford wants people not to understand normalisation, it's going the right way about it. Even wikipedia (see the BCNF article, which discusses this loss of representation by comparing BCNF with 3NF, and reports it as normalisation to BCNF being "unachievable" for some schemas - I wish I'd thought of that description, but having seen it I now sometimes use it) does better than that.
Tom
November 9, 2011 at 6:22 am
Steve Jones - SSC Editor (11/8/2011)
I'm sure you'll pass me. I am down to about 150 posts a month, too busy with other stuff and most questions are being handled.
You've got a lot of proxy hands/eyes/brains on the site these days that you didn't a few years ago. Sign of a mature site. (Business-cycle-wise, not necessarily any other measure of maturity. Especially when subjected to my sense of humor!)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2011 at 6:24 am
GilaMonster (11/8/2011)
How can BCNF be sensibly discussed if the earlier normal forms aren't known?
Same way you can teach Algebra without learning Arithmetic first. You just ... uh ... well ... hmmm ... you ... oh never mind!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2011 at 6:27 am
Koen Verbeeck (11/8/2011)
L' Eomot Inversé (11/8/2011)
The next bad normalisation myth is that normalisation always increases the amount of storage required - a database in 3NF will require more storage than the same data expressed in an unnormalised (not even 2NF) wayThat is a strange myth. How can a technique that reduces redundancy lead to more storage? :blink:
I once asked someone who believed it why he believed it. The answer I got was that normalisation increased the number of tables, so of course it increased the size of the database. He wouldn't budge from believing that this total non-sequitur was an absolutely correct logical proof of the myth. :angry:
Tom
November 9, 2011 at 6:35 am
L' Eomot Inversé (11/9/2011)
Koen Verbeeck (11/8/2011)
L' Eomot Inversé (11/8/2011)
The next bad normalisation myth is that normalisation always increases the amount of storage required - a database in 3NF will require more storage than the same data expressed in an unnormalised (not even 2NF) wayThat is a strange myth. How can a technique that reduces redundancy lead to more storage? :blink:
I once asked someone who believed it why he believed it. The answer I got was that normalisation increased the number of tables, so of course it increased the size of the database. He wouldn't budge from believing that this total non-sequitur was an absolutely correct logical proof of the myth. :angry:
How much table overhead would it take for that??? Wow!
On the other hand .... Once had someone explain to me that 5NF means you do things like storing dates as references to tables of days, months, years, hours, et al., "since a date really isn't atomic data". Design the tables right (wrong), and you could easily multiply the data-storage for date columns tremendously! A GUID FK to a table for the day-of-month, another to a table of months, another to a table of years, four more for hours, minutes, second, and milliseconds, and you have 7 GUIDs instead of a single DateTime column. That would certainly "normalize" the data while expanding it!
So, all you have to do to accomplish this is completely and utterly misunderstand the whole point and concept of normalization, as well as the whole purpose of surrogate keys, and have a serious fetish for GUIDs, and you could really, really make that person right. Just takes a really clever idiot!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2011 at 6:42 am
Koen Verbeeck (11/8/2011)
L' Eomot Inversé (11/8/2011)
The next bad normalisation myth is that normalisation always increases the amount of storage required - a database in 3NF will require more storage than the same data expressed in an unnormalised (not even 2NF) wayThat is a strange myth. How can a technique that reduces redundancy lead to more storage? :blink:
L' Eomot Inversé (11/8/2011)
The third myth is that normalisation is difficult to understand, intellectually challenging. It isn't - it's just that it's pretty rare that anyone describes it in everyday terms instead of in academic jargon.That is a myth? I still have troubles understanding it fully. But that is maybe because it was explained to me in academic jargon by a professor old enough to have probably teached Codd himself.
Luckily I'm a BI guy, all I do is denormalize 😀
In our case, we normalize first. Maybe we are working too hard.:-D
November 9, 2011 at 6:47 am
L' Eomot Inversé (11/8/2011)
Roy Ernest (11/8/2011)
Hey everyone, I am hijacking the thread with an "Off Topic"I am planning on a presentation for my user group "DBA MythBusters" (Courtesy Paul Randal) His idea is brilliant. It is a fun filled session. I would like to do one like that for our user group. If anybody have suggestions on what Myth to bust please, please let me know. The audience is 100 to 200 level.
Here are a couple of normalisation myths; probably more verbose thanm I should be, and it's a bit of a rant.
To me the worst myth is "The database should have nothing to do with business logic", when the whole point of designing schemata with (primary and foreign and unique) key constraints and domain (column type, non-nullability, and check) constraints and doing normalisation is to ensure that the very structure of the schemata and their constraints enforces business rules, to make it easier to write code which will not result in data that is invalid according to business rules, and to make it very difficult (if not impossible) to write code that will allow data in the database to violate those business rules.
The next bad normalisation myth is that normalisation always increases the amount of storage required - a database in 3NF will require more storage than the same data expressed in an unnormalised (not even 2NF) way; in fact normalisation almost always reduces the storage required - and each step from 1NF to 5NF reduces datbase size; denormalisation is sometimes needed to gain performance in an effectively read-only database (typical BI or DWH usage) but that is not a size issue; certain 3NF schemata have no 4NF equivalents which enforce all the business rules enforced in the 3NF version so it is sometimes necessary to keep some tables below 4NF in order to preserve enforcement of required referential integrity, but this is extremely rare and even when it happens doesn't usually cause much size increase.
The third myth is that normalisation is difficult to understand, intellectually challenging. It isn't - it's just that it's pretty rare that anyone describes it in everyday terms instead of in academic jargon.
A fourth normalisation myth that is very damaging is propagated by the happy band of anti-null fundamentalists. They teach that all normalisation is rendered impossible as soon as you have a nullable column in your table. This is extremely damaging because naive people believe them, and although they have to work with rdbms products that allow nulls and soon discover that nulls are essential for some of their work they decide not to attempt normalisation, because they've been told that for their null-permitting systems it is impossible. This leads them to produce incredibly bad schemata that leave the database wide open to application errors that ought to have been prevented by key constraints. Disliking nulls is very sensible (I hate the things myself, except when I really need them), pretending they are not needed is stupid, and claiming they make normalisation impossible is irresponsible vandalism.
This is brilliant. Someone has to make this into an article. Just copy paste it and put Toms name in it as the author. 🙂
-Roy
November 9, 2011 at 7:03 am
L' Eomot Inversé (11/9/2011)
Koen Verbeeck (11/8/2011)
L' Eomot Inversé (11/8/2011)
The next bad normalisation myth is that normalisation always increases the amount of storage required - a database in 3NF will require more storage than the same data expressed in an unnormalised (not even 2NF) wayThat is a strange myth. How can a technique that reduces redundancy lead to more storage? :blink:
I once asked someone who believed it why he believed it. The answer I got was that normalisation increased the number of tables, so of course it increased the size of the database. He wouldn't budge from believing that this total non-sequitur was an absolutely correct logical proof of the myth. :angry:
Ah. So one big ball of lead weighs less than 1,000 feathers?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 9, 2011 at 7:07 am
L' Eomot Inversé (11/8/2011)
The third myth is that normalisation is difficult to understand, intellectually challenging. It isn't - it's just that it's pretty rare that anyone describes it in everyday terms instead of in academic jargon.
Anyone have good examples of it being described in everyday terms? Books (to learn by) preferred....
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2011 at 7:09 am
Koen Verbeeck (11/9/2011)
L' Eomot Inversé (11/9/2011)
Koen Verbeeck (11/8/2011)
L' Eomot Inversé (11/8/2011)
The next bad normalisation myth is that normalisation always increases the amount of storage required - a database in 3NF will require more storage than the same data expressed in an unnormalised (not even 2NF) wayThat is a strange myth. How can a technique that reduces redundancy lead to more storage? :blink:
I once asked someone who believed it why he believed it. The answer I got was that normalisation increased the number of tables, so of course it increased the size of the database. He wouldn't budge from believing that this total non-sequitur was an absolutely correct logical proof of the myth. :angry:
Ah. So one big ball of lead weighs less than 1,000 feathers?
Monty Python's Quest for the Holy Grail
And what else floats?
Lead!
Churches!
Really small rocks!
One of the best depictions of "the wisdom of the masses" ever filmed. So, yeah, there are people who think that way.
I say we blame the whole thing on violent video games and classical music.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2011 at 7:35 am
L' Eomot Inversé (11/9/2011)
GilaMonster (11/8/2011)
How can BCNF be sensibly discussed if the earlier normal forms aren't known?As far as I can tell, it can't. Obviously it can be discussed, but not , in my opinion, sensibly.
Also, omitting the earlier forms means that you never see the serious conceptual break between 3NF and 4NF (more strictly between EKNF and BCNF) where the representation principle goes out of the window, so that you don't realise that it can be very important to retain some tables at a lower normalisation level than BCNF in order to prevent the normalisation process destroying the capability for key constraints to enforce some essential business rules, a capability that exists all the way up to EKNF (and for most schemas, at all higher levels too - but the exceptions can ruin a project if it doesn't watch out for them).
If Stanford wants people not to understand normalisation, it's going the right way about it. Even wikipedia (see the BCNF article, which discusses this loss of representation by comparing BCNF with 3NF, and reports it as normalisation to BCNF being "unachievable" for some schemas - I wish I'd thought of that description, but having seen it I now sometimes use it) does better than that.
There are some novel approaches to teaching normalization out there. I picked this up because it looked amusing and I wanted to see how well they did and what they covered. I haven't finished it yet, but they spend a good part of the book going through normalization and I think they do a pretty good job.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 9, 2011 at 7:49 am
Am I the only 1 who wants to answer... if you don't know that, then why are you teaching this stuff??
http://www.sqlservercentral.com/Forums/Topic1202806-150-1.aspx
Viewing 15 posts - 31,516 through 31,530 (of 66,756 total)
You must be logged in to reply to this topic. Login to reply