SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Heath Normal Form


Heath Normal Form

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26195 Visits: 12506
Comments posted to this topic are about the item Heath Normal Form

Tom

bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16011 Visits: 25280
Without prior knowledge of the QOD author I surmised it was you...

Now a general question for yourself .... in your experiences, around the world, what would you say are the percentage of DBs in 4NF and / or higher (5NF,6NF).

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26195 Visits: 12506
bitbucket-25253 (11/12/2011)
Without prior knowledge of the QOD author I surmised it was you...

Now a general question for yourself .... in your experiences, around the world, what would you say are the percentage of DBs in 4NF and / or higher (5NF,6NF).

Well, I would guess about 50%, or maybe a bit lower, are in 4NF or higher.

actual counts from experience:
In 2007: system with 9 databases, 7 in 4NF or higher, 1 in 2NF (denormalised Management Information database), 1 not even in 1NF (arrogant and incompetent people who wouldn't follow instructions).
In 2002: system with 3 databases, 1 in BCNF and 2 not even in 1NF.
in 2000: system with 2 DBs, both in 4NF or higher.
Before 2000: most everything 4NF or higher, except MI stuff.

Many data warehouse/business intelligence/management information databases are not in 4NF, because they've been deliberately denormalised (which is reasonably safe because the usual concerns about update and insertion anomalies don't apply, the method of getting data into them is very different from other databases) to reduce join complexity of big queries in exchange for an increase of storage requirement through the increased redundancy of the data. Sometimes some parts of such databases aren't even in 3NF, some not even in 2NF.

At the other end, some databases have never had any normalisation done at all. They are not in 2NF, because nobody involved in the design knew anything about normalisation and thought they just had to code around all the anomalies, often not in 1NF because someone thought nonatomic fields were a good idea and primary keys an uneeded luxury. Unless these systems are very simple and don't get caught up in enhancements to provide extra features they can be a real pain.

It's fairly common for people to aim for 3NF (whether by guess or by using Bernstein's algorithm) and that usually ends up conforming to EKNF or BCNF (if they use Bernstein's algorithm it's guaranteed to be at least EKNF). Since any 3NF table which doesn't have overlapping candidate keys is guaranteed to be in BCNF, and overlapping candidate keys are rare, it's usually BCNF. Multivalued dependencies are also pretty rare, so probably most things that have been aimed at 3NF actually hit 4NF. I don't have any real feel for how rare join dependencies are, so I don't have a feel for how often 5NF is hit by accident, but I suspect it's quite often.

It's less common for people to aim for 4NF or for 5NF than for 3NF or EKNF, but that probably doesn't cause the proportion of DBs in 4NF or higher to be much lower, simply because most 3NF schemas are 4NF as noted above.

Of course some people will tell you that almost all SQL databases are not in any normal form because they have at least one column in at least one table that allows null. If you believe that pernicious claptrap, use a figure of about 1% instead of the about 50% I suggested above.

Tom

M&M
M&M
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6135 Visits: 3913
Tom,
When I saw this question related to "normal forms", I knew it must be from you. Thanks for the question as we got to know some interesting facts.

M&M
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36128 Visits: 11361
I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points :-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67577 Visits: 18570
Thanks Tom for continuing to educate us on NF



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26195 Visits: 12506
SQL Kiwi (11/13/2011)
I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points :-)

Well, I'm glad you liked it, even for that reason. At last I have managed to produce a question that most people get right before they can read the answer in the next day's update from sqlservercentral. massive 67% so far. Cool

But I wonder how long it will be before someone claims it's a trick question? Hehe

Tom

Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68569 Visits: 9671
L' Eomot Inversé (11/13/2011)
SQL Kiwi (11/13/2011)
I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points :-)

Well, I'm glad you liked it, even for that reason. At last I have managed to produce a question that most people get right before they can read the answer in the next day's update from sqlservercentral. massive 67% so far. Cool

But I wonder how long it will be before someone claims it's a trick question? Hehe



I owe 2 points to google on this one.


I first assumed it was a typo from a enguish as my 8th language. Then I saw it was your question so I assumed word play. Then wiki[pedia] gave it to me straight :-D.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63606 Visits: 13298
Nice question Tom, thanks.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17313 Visits: 7421
Thanks for the question, Tom.
Really educational.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search