Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Normal Forms


Normal Forms

Author
Message
Steve Jones
Steve Jones
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: Administrators
Points: 36145 Visits: 18748
OK, I hesitate to ask this, but I would like to round this out.

I am looking for a basic set of articles that explains first, second, third, maybe more, normal forms. Not like the argument going on in the Relational Theory, not from a high academic, this is correct, Date v Codd.

I am looking for a basic explanation of what the normal forms mean from a practical perspective. Take a couple of simple tables and break them down to be normalized. Something like this, perhaps by working backwards in AdventureWorks or building a simple Order/Sales set of tables.

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12009
How quickly do you want it?

I guess it's just the definitions, examples of how it changes something (before and after) and example of what goes wrong if you don't do it. No high-falutin' theory or any of that junk, and nothing controversial? Certainly no mention of NULLs because that would start a war.

Incidentally, I think I saw something a few weeks ago - an article on SQL Server Central (or perhaps written by a threadizen but posted somewhere else) that covered 1NF through 3NF very well.

If covering normal forms 1 through 5 WITHOUT the intermediates like EKNF and BCNF because the article can get too long, and leaving out 6NF (other than to say that it is applicable to schemas that have to handle time-dependent data, and avoids some of the problems that people often run into with that) because it is too hard for this sort of article, and leaving out DKNF (because most things don't have a DKNF form, there's no way to normalise them to DKNF) would be OK, I can do it if it's not urgent. But I'm heading back to the UK next Wednesday so wouldn't be able to start on it before about May 7th. Also, I'm not sure that covering all of forms 1 to 5 in a single article is going to produce something short enough, it might be better to split it in half with 1NF, 2NF and 3NF in one article; 4NF, 5NF and the non-technical one or two liner on 6NF in another.

Incidentally, the 6NF mentioned above is a Date invention and I regard it as a very good thing despite disagreeing with him on a couple of other things (MVLs and NULL, mostly). The other thing that used to be called 6NF is what is now called DKNF and is pretty useless (its uselessness is something else that Date and I have a common view on). Don't get the impression from the stuff going on in the normalisation topic in the relational theory forum that I think Date is wrong about everything, or that David Portas thinks Codd was wrong about everything.

Tom

Steve Jones
Steve Jones
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: Administrators
Points: 36145 Visits: 18748
Really the basics, Tom. Something between the DB Design one Paul White wrote and the crazy relational debate. A "normalization" for dummies, and I'd like to have a separate article for each one.

1NF
2NF
3NF

building on each other. You could go 4 and 5, or BNF if you want.

No hurry. Just filling Spackle holes here with some ideas to round out content on the site.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12009
OK, Steve, separate article for each understood. I'll take it.

Tom

Tom

Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5699 Visits: 7660
I can take a swing at this if Tom is feeling time pressured, I've pretty much avoided the entire debate out of self defense. Did you want these spackle-style (page and a half or so) or more complete full article per NF? I will say that Tom or someone of his knowledge and research would be able to complete the 4NF+ articles much faster then I. I know the basics of them but would need to re-research to keep from having foot in mouth disease.

EDIT: D'oh, sniped. All yours Tom. :-)


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
Steve Jones - SSC Editor (4/28/2011)
OK, I hesitate to ask this, but I would like to round this out.

I am looking for a basic set of articles that explains first, second, third, maybe more, normal forms. Not like the argument going on in the Relational Theory, not from a high academic, this is correct, Date v Codd.

I am looking for a basic explanation of what the normal forms mean from a practical perspective. Take a couple of simple tables and break them down to be normalized. Something like this, perhaps by working backwards in AdventureWorks or building a simple Order/Sales set of tables.

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


You must be a glutton for punishment. :-D What are you going to ask for next? An article that justifies why NULLs should be removed from all database systems? Hehe

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
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: Administrators
Points: 36145 Visits: 18748
HA, I like NULLs. I might ask for two articles, one pro and one con.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12009
Steve Jones - SSC Editor (4/30/2011)
HA, I like NULLs. I might ask for two articles, one pro and one con.


maybe David Portas could write the pro and Jeff could write the con; that way they would be extremely interesting articles! HeheHeheHehe

Tom

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
Tom.Thomson (4/30/2011)
Steve Jones - SSC Editor (4/30/2011)
HA, I like NULLs. I might ask for two articles, one pro and one con.


maybe David Portas could write the pro and Jeff could write the con; that way they would be extremely interesting articles! HeheHeheHehe


I'm not sure which side is which there. If you mean that I'd have to write an article that says we should do away with NULLs, I have to ask... do you know what "Obese Opportunity" means? :-P

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
Steve Jones - SSC Editor (4/30/2011)
HA, I like NULLs. I might ask for two articles, one pro and one con.


I like NULLs, as well. I was just thinking of the insanely long threads where people keep saying that NULLs aren't a part of a true relational database and Codd this and Date that and yada-yada-yada. I keep thinking "who gives a rat's patooti?" and that I hope I never run into a true relational database because it will even make Oracle look good. :-D

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12009
Jeff Moden (4/30/2011)
Steve Jones - SSC Editor (4/30/2011)
HA, I like NULLs. I might ask for two articles, one pro and one con.


I like NULLs, as well. I was just thinking of the insanely long threads where people keep saying that NULLs aren't a part of a true relational database and Codd this and Date that and yada-yada-yada. I keep thinking "who gives a rat's patooti?" and that I hope I never run into a true relational database because it will even make Oracle look good. :-D

I like NULLs too. Of course I believe that a true relational system has to support them (after all, it has to support outer union and outer join, and they will certainly produce nulls). So I don't think a true relational system has to do badly.

But I do love reminding the anti-null fundamentalists that Ted Codd, the inventor of their holy relational model, stated clearly and plainly and very publicly that a system without decent NULL support could not be a relational system.

Tom

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
That's why I'm glad you decided to take this on, Tom. No BS and personal opinions would be identified as such instead of being identified as gospel. I think you'd bring the practical side to the article that Steve is looking for someone to write and that I'd like to read. I almost can't wait.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
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: Administrators
Points: 36145 Visits: 18748
I don't care who does it, but if Tom gets one in while anyone else's is in the Q, I'll go with Tom.

Tom, no great hurry and I'll let you know if I get something else. Doesn't have to be long. I'd figure 1-1.5 page of text with a few examples that show tables being moved from non-1NF->1NF would be fine.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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