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»»»

First Normal Form Expand / Collapse
Author
Message
Posted Thursday, June 30, 2011 6:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 262, Visits: 920
1) Good job on the topic: it caught my attention. That alone is a considerable feat in a world of information overload.
2) Where's the rest of it? The tone starts out like a story and I was enjoying it up to the rather short last paragraph but there was no "next page(s)" button. I'm sure writing a long technical article can be daunting - but I was hoping the tale you started telling would continue (at least until my coffee had cooled enough to drink)
3) You gave two example rows of a non-1NF table. That's a helpful visualization. The follow up description proposed how one might "fix" the design - another illustration would have been equally helpful. Many readers here are already familiar with NF and table design, however those reading about 1NF might not be as confident with those concepts; some might think fewer (non-normal) tables are "easier to understand."
4) Provide some footnotes linking to more information regarding your apparent dislike for NULL values. I know it's a subject that's been done many times, but the audience reading an article on 1NF might still not have an informed position on it. (hard for veteran DBAs to remember new people enter this field every day)
5) Keep writing. I hope your next topic catches my attention when it crosses my inbox in the future. :)
Post #1134372
Posted Thursday, June 30, 2011 6:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 17, 2014 5:39 AM
Points: 13, Visits: 183
Almost exactly the same reaction here too. I don't understand who this is aimed at - not the beginner, certainly. It serves little purpose for anyone else.
Post #1134373
Posted Thursday, June 30, 2011 6:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:03 AM
Points: 30, Visits: 746
From what I've learned on relational theory I understand that every attribute of a relation is composed of a name and a domain, a type.

As Hugh Darwen define it (An introduction to Relational Database Theory, p40), to me a type (or domain) is a set a values.

In the integer set of possible values. I expect to find integers, nothing else. I guess I'm wrong then...

Tom.Thomson (6/30/2011)

For example a domain of unsigned integers range 0 to 255 contains the 256 integers 0...255 which are fully defined elements and the BOTTOM element about which nothing is known except that it a member of that domain;

If nothing is known about this element, does it really needs to exists ?
Post #1134377
Posted Thursday, June 30, 2011 6:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 1,059, Visits: 1,794
I thought it was a very good article. Perhaps a few more illustrations of the "normalized" tables that you described (ER diagram maybe?). As to the Anti-NULL zealots... you aren't going to get a lot of people to agree by being a jerk, so stop trying to be a jerk. To Steven's previous post... What about the Empty Set, surely that is a subset of the domain or did my set theory professor get that one wrong, wouldn't the empty set be equivalent to the BOTTOM or Null?
Post #1134411
Posted Thursday, June 30, 2011 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
JimTheWhip (6/30/2011)
I started to forward this to a colleague who is just starting to design databases. After reading it thoroughly I thought better of it. I think the author has just started designing databases himself.

You would be wrong by something over 40 years, then; or if you want to restrict to relational databases, something well over thirty years but not as many as forty.

Why in an article about designing a database would you confuse it by saying it's ok to denormalize columns if the application is going to take care of it.

1NF is only concerned with what the relational calculus language sees and manipulates, not with things which may or may not be done elsewhere. So I'm not advocating any form of denormalisation, just saying that atomicity (so far as the relational calculs is concerned) is what's in question.
Do you think it would make any difference if the list of numbers were encrypted in such a manner that the relational calculus language could not tell that it's a list? If so, why?

Fortunately the (approximately) relational calculus language we mostly use, SQL, has no list manipulation features built in so it's absolutely clear that if a list (of something other than characters) is held in a table and sometimes is (or at some point in the future is likely to be) deconstructed using (for example) SQL character string functions then the relation is not in 1NF. That, I think, is what people need to understand.


Tom
Post #1134421
Posted Thursday, June 30, 2011 7:09 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, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
JimTheWhip (6/30/2011)

I question the value of technique "Usually a still better solution (better by far) is to introduce a new table OfficeFax that has a name column and a FaxNumber column". What?!?! You would create a PhoneTypes table listing the types of phone numbers you're going to store with a primary key and a description of each. Then create a PhoneNumbers table with a number column and a couple foreign keys relating it to the original information table and the PhoneTypes table. This creates a many to many relationship saying for a given phone type you can have many people that have them and many people may have a given phone types.


QFT

I was a bit astounded that we'd name the phone numbers table anything other than phone numbers and then add a types table myself. Querying it to bring back a flat record for some form of processing can be a bit of a pain, but it's that or a series of columns for phone numbers and no flexibility for when we start putting more than home, business, fax, cell phone numbers on a record.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1134433
Posted Thursday, June 30, 2011 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
Mike Dougherty-384281 (6/30/2011)
1) Good job on the topic: it caught my attention. That alone is a considerable feat in a world of information overload.
2) Where's the rest of it? The tone starts out like a story and I was enjoying it up to the rather short last paragraph but there was no "next page(s)" button. I'm sure writing a long technical article can be daunting - but I was hoping the tale you started telling would continue (at least until my coffee had cooled enough to drink)

Well, Steve had asked for a series of very short articles on normal forms. That was just the first one. Two more are completed and are providionally scheduled to appear mid July and late July. I am going to write at least two more, and see if Steve accepts them. And then maybe a slightly longer article discussing the conficts between higher normal forms and the representation principle.
3) You gave two example rows of a non-1NF table. That's a helpful visualization. The follow up description proposed how one might "fix" the design - another illustration would have been equally helpful. Many readers here are already familiar with NF and table design, however those reading about 1NF might not be as confident with those concepts; some might think fewer (non-normal) tables are "easier to understand."

With the length constraints Steve proposed there wasn't room for the illustration; but maybe I was interpreting the constraint too strictly, and should have added it.
4) Provide some footnotes linking to more information regarding your apparent dislike for NULL values. I know it's a subject that's been done many times, but the audience reading an article on 1NF might still not have an informed position on it. (hard for veteran DBAs to remember new people enter this field every day)

My apparent dislike for NULL? I'm more used to being accused by David and Steven993 of being a null-loving heretic! . I'm all for NULL used correctly, when it's needed. I don't like some of the things SQL has done with NULL (for example the sum of an empty set should be 0, not NULL), and I don't like it being misused (see my reply to George above) and I really don't want to get into a big NULL debate here.
5) Keep writing. I hope your next topic catches my attention when it crosses my inbox in the future. :)
Thanks for the encouragement.


Tom
Post #1134434
Posted Thursday, June 30, 2011 7:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
Wow... Nice Introduction paragraph...

I think some links to (or a mention of) external referencesin the article would have helped the points it was making. Two external references in an article can save you from writing an encyclopedia afterwards.

The article had a tone and feel to it that makes one think Tom had to deal with some abusive database architecture in the past.
That made me sad.
I do not like feeling sad. Few people do.
Post #1134444
Posted Thursday, June 30, 2011 7:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:21 PM
Points: 142, Visits: 134
Setting aside the fact that I'd rather normalize this further than just 1NF, I want to understand your argument better:

Well, NULL should always mean "The database doesn't contain this value", so it would be used in the case where the value is applicable but unknown for one reason or another as well as in the case where there is no value because it's not applicable; if you want to be able to tell for certain that the value is inapplicable, you can't use NULL unless you can guarantee that there are no cases where the value is applicable but unknown.


What you're suggesting is that in the case where "Person A: Cannot possibly have a phone number" it would be better to use a garbage value like 000-000-0000,
but in the case where "Person A: Could have a phone number but we don't know it" you suggest to use NULL?

What is the fundamental reasoning behind that decision? Is it simply based off the original definitions of NULL from Codd et al.?
Post #1134448
Posted Thursday, June 30, 2011 8:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 19, 2014 1:37 PM
Points: 271, Visits: 317
The minor issue I have with NULL is that they may not be universally accounted for or perhaps understood is a better term. I once had a vendor send me a flat file with the word "NULL" as an column entry and he expected me to treat it as a NULL value. Also, I don't like having to specifically exclude them as part of a select criteria. For example, if a column allows nulls, and I need to find all rows not having a specific value, I need to account for that NULL. Which is fine if I know that, but I've had many developers approach me asking why their query isn't returning the correct recordset not aware of the need to specifically exclude NULLs. I personally prefer defaulting spaces, but this presents an issue with date and numeric fields.


Post #1134493
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse