SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tom Thomson / Heath Normal Form / Latest Posts

RE: Heath Normal Form

Britt Cluff — Tue, 15 Nov 2011 06:00:55 GMT

Thanks for the question.

RE: Heath Normal Form

Rick.Cornell.SME — Mon, 14 Nov 2011 16:42:08 GMT

I also agree that it was an interesting question and I had to think about for awhile.... :w00t:

RE: Heath Normal Form

KWymore — Mon, 14 Nov 2011 16:08:31 GMT

Nice question, interesting stuff. Thanks Tom.

RE: Heath Normal Form

L' Eomot Inversé — Mon, 14 Nov 2011 09:43:27 GMT

[quote][b]SanDroid (11/14/2011)[/b][hr]IMHO: I have yet to find a database that comes with any current business software that is fully normalized to 4NF in every physical data structure. After a quick rundown of what is in my current production environment many of them fail simply becuase they store duplicate denormalized historical data in the transactional database for reporting and auditing. We can talk all day long about how this could be done better, faster, stronger. [/quote]Actually I would expect historical data held for reporting to be denormalised, but I would also expect it to be in a separate MI database (I'm a dinosaur - I haven't learnt to type BI instead of MI). But the stuff for audit should be the stuff that was generated in real time by the real transaction so quite separate from reporting data.[quote]I wonder how many of us have got anyone to agree to a massive budget needed to fully normalize.[/quote]Once a system that should be normalised has been built unnormalised, no one has budget to do proper normalisation until shortly after the brown stuff meets the blades.

RE: Heath Normal Form

OzYbOi d(-_-)b — Mon, 14 Nov 2011 08:41:40 GMT

Thanks for the question Tom. :-)

RE: Heath Normal Form

SanDroid — Mon, 14 Nov 2011 07:58:09 GMT

[quote][b]L' Eomot Inversé (11/13/2011)[/b][hr][quote][b]bitbucket-25253 (11/12/2011)[/b][hr]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).[/quote]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....[/quote]IMHO: I have yet to find a database that comes with any current business software that is fully normalized to 4NF in every physical data structure. After a quick rundown of what is in my current production environment many of them fail simply becuase they store duplicate denormalized historical data in the transactional database for reporting and auditing. We can talk all day long about how this could be done better, faster, stronger. I wonder how many of us have got anyone to agree to a massive budget needed to fully normalize.For some reason large scale business applications (like JDE and others) have many decission makers in bussiness believing that any database using under 10 gigabytes of data storage is small and running on systems way bellow Midrange.:cool:

RE: Heath Normal Form

SanDroid — Mon, 14 Nov 2011 07:40:53 GMT

First let me say interesting question. I had heard about this in the 80's but had really dropped it along with cars that run on water and the gushot from the grassy knoll uuntiltoday.Thank you for the reeducation commrad.BTW: Love the profile picture.

RE: Heath Normal Form

mtassin — Mon, 14 Nov 2011 07:38:26 GMT

[quote][b]Hugo Kornelis (11/14/2011)[/b][hr][quote][b]martin.whitton (11/14/2011)[/b][hr]I never use BI in my work. Yet, I like QotD questions about BI, because they force me to look into technology I normally don't use, and help me learn. I also like trivia questions like this one, because I like to know a bit more than just the bare technical knowledge my job requires - and I also hope that this question will contribute a bit to making the issue and importance of normalization more well-known.[/quote]I could surprise you here. After setting up my scripts to capture wait state differentials from my production server. I had to bumble my way through PowerPivot to develop some nice quick and easy to use dashboards on the BI server so I could analyze them easily and simply.If I didn't feel like a bumbling PowerPivot fool, I think I'd write a PowerPivot article for SSC. Some of the rest of the BI stuff from MS can be wonky (Report Models/Report Builder amaze me at how stupid some of the parts of them work compared to something like Crystal Reports, and SSAS makes me scream when I want to do something that seems to be relatively simple and leaves me befuddled), but PowerPivot is a nice simple and powerful tool (I guess until I want to calculate a median, since it's an SSAS derivative that will likely cause me to start the screaming fest all over again).

RE: Heath Normal Form

rfr.ferrari — Mon, 14 Nov 2011 04:06:17 GMT

thanks for the question, Tom!!!!!

RE: Heath Normal Form

Chris Houghton — Mon, 14 Nov 2011 03:54:25 GMT

My powers of guess work are strong today :)

RE: Heath Normal Form

Hugo Kornelis — Mon, 14 Nov 2011 03:51:42 GMT

[quote][b]martin.whitton (11/14/2011)[/b][hr]However, the fact that Hugo admits to having to Google this one might suggest that this question is a little on the esoteric side, and that you could be quite good at your job without knowing or caring about the answer![/quote]I think that if "you could be quite good at your job without knowing or caring about the answer" is the measure for the QotD, then we could remove, depending on the exact job people are doing, somewhere between 80-90% of all questions.I never use BI in my work. Yet, I like QotD questions about BI, because they force me to look into technology I normally don't use, and help me learn. I also like trivia questions like this one, because I like to know a bit more than just the bare technical knowledge my job requires - and I also hope that this question will contribute a bit to making the issue and importance of normalization more well-known.

RE: Heath Normal Form

martin.whitton — Mon, 14 Nov 2011 03:46:38 GMT

[quote][b]L' Eomot Inversé (11/14/2011)[/b][hr][quote][b]martin.whitton (11/14/2011)[/b][hr][quote][b]paul s-306273 (11/14/2011)[/b][hr]......I don't for a moment imagine that knowing that Chris Date suggested that Heath Normal Form night be a more suitable name than Boyce-Codd Normal Form would help anyone in their work (unless perhaps their work was writing a book about the history of relation theory development in the early years). But a lot of people find this kind of trivia fun, and there's always the hope that some people will read up either the papers referenced in the explanation or some of the things they find when googling for an answer and learn something useful from that.[/quote]Those are valid points, Tom.I'm quite happy to see questions like this appear from time to time; they just don't suit me personally. And maybe I'm just a little bit grumpy today because I lost my 2 points!

RE: Heath Normal Form

L' Eomot Inversé — Mon, 14 Nov 2011 03:34:15 GMT

[quote][b]martin.whitton (11/14/2011)[/b][hr][quote][b]paul s-306273 (11/14/2011)[/b][hr]I'd like to say I don't like these questions - but looking at the posts I appear to be in the minority!Anybody else out there with me?[/quote]I have to say I'm with you on this one, Paul.I do acknowledge that different people have different styles of learning and working, and for some people memorizing all the nuances of normalization theory may genuinely help them in their work. However, the fact that Hugo admits to having to Google this one might suggest that this question is a little on the esoteric side, and that you could be quite good at your job without knowing or caring about the answer![/quote]I don't for a moment imagine that knowing that Chris Date suggested that Heath Normal Form night be a more suitable name than Boyce-Codd Normal Form would help anyone in their work (unless perhaps their work was writing a book about the history of relation theory development in the early years). But a lot of people find this kind of trivia fun, and there's always the hope that some people will read up either the papers referenced in the explanation or some of the things they find when googling for an answer and learn something useful from that.

RE: Heath Normal Form

martin.whitton — Mon, 14 Nov 2011 03:24:30 GMT

[quote][b]paul s-306273 (11/14/2011)[/b][hr]I'd like to say I don't like these questions - but looking at the posts I appear to be in the minority!Anybody else out there with me?[/quote]I have to say I'm with you on this one, Paul.I do acknowledge that different people have different styles of learning and working, and for some people memorizing all the nuances of normalization theory may genuinely help them in their work. However, the fact that Hugo admits to having to Google this one might suggest that this question is a little on the esoteric side, and that you could be quite good at your job without knowing or caring about the answer!

RE: Heath Normal Form

Andrew Watson-478275 — Mon, 14 Nov 2011 02:45:10 GMT

Google too (it's been a while since college, and theat's the only time I've really known all the names of things). Of course by now, the top hit on Google for "Heath Normal Form" is this question itself!

RE: Heath Normal Form

Hugo Kornelis — Mon, 14 Nov 2011 02:34:04 GMT

[quote][b]L' Eomot Inversé (11/13/2011)[/b][hr]But I wonder how long it will be before someone claims it's a trick question? :hehe:[/quote]Trick question! :-PJust kidding, of course. Thanks for the question. I think I encountered the term Heath Normal Form once, but too long ago to be able to answer the question without the help of my good friend Google.

RE: Heath Normal Form

paul s-306273 — Mon, 14 Nov 2011 01:50:16 GMT

I'd like to say I don't like these questions - but looking at the posts I appear to be in the minority!Anybody else out there with me?

RE: Heath Normal Form

Stewart "Arturius" Campbell — Mon, 14 Nov 2011 01:44:00 GMT

Thanks for the question, Tom.Really educational.

RE: Heath Normal Form

Koen Verbeeck — Sun, 13 Nov 2011 23:46:29 GMT

Nice question Tom, thanks.

RE: Heath Normal Form

Ninja's_RGR'us — Sun, 13 Nov 2011 17:16:53 GMT

[quote][b]L' Eomot Inversé (11/13/2011)[/b][hr][quote][b]SQL Kiwi (11/13/2011)[/b][hr]I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points :-)[/quote]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:[/quote]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.

RE: Heath Normal Form

L' Eomot Inversé — Sun, 13 Nov 2011 15:38:10 GMT

[quote][b]SQL Kiwi (11/13/2011)[/b][hr]I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points :-)[/quote]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:

RE: Heath Normal Form

SQLRNNR — Sun, 13 Nov 2011 14:35:49 GMT

Thanks Tom for continuing to educate us on NF

RE: Heath Normal Form

Paul White — Sun, 13 Nov 2011 09:00:27 GMT

I really liked this question. Not least because I didn't have to read past answer possibility #1 to get my two points :-)

RE: Heath Normal Form

mohammed moinudheen — Sun, 13 Nov 2011 08:33:45 GMT

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.

RE: Heath Normal Form

L' Eomot Inversé — Sun, 13 Nov 2011 03:34:52 GMT

[quote][b]bitbucket-25253 (11/12/2011)[/b][hr]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).[/quote]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.

RE: Heath Normal Form

bitbucket-25253 — Sat, 12 Nov 2011 13:52:00 GMT

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

Heath Normal Form

L' Eomot Inversé — Sat, 12 Nov 2011 13:45:29 GMT

Comments posted to this topic are about the item [B]Heath Normal Form[/B]