June 30, 2011 at 7:09 am
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.
June 30, 2011 at 7:12 am
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?:w00t: I'm more used to being accused by David and Steven993 of being a null-loving heretic! 😀 😀 :-P. 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
June 30, 2011 at 7:30 am
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.
June 30, 2011 at 7:36 am
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.?
June 30, 2011 at 8:26 am
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.
June 30, 2011 at 10:32 am
I appreciated the article. More so, I appreciated the author's replies in these discussions. Very informational and more so, a great example of professional conduct in response to criticism. Thanks for being a good role model. I look forward to reading your future articles.
June 30, 2011 at 10:51 am
CELKO (6/30/2011)
The Relational Model for Database Management Version 2 by Dr. E. F. Codd Section 1.8 "Key and Referential Integrity" .....
It is always good to see somone at your level add thier comments.
Could you add some to the dicussion on NULL values?
It seems some of the people commenting are not familiar with the implimentation of NULL as being an absence of any value.
Example : If a person subscibes to a service that is canceled, never recieved, and never invoiced should the amount payed be NULL or 0?
Is the use of NULL values in a table column a question of database architecture or an application business rules question?
June 30, 2011 at 11:12 am
CELKO (6/30/2011)
He does not talk about candidate keys, super keys, etc.
Yes indeed.
Notice that he mentioned the candidate key, but dosen't talk about it.
The term "key" has been used in the computing field for a long time, and with a great variety of meanings. In the relational model the term is normally qualified by the adjectives "candidate,", "primary," and "foreign," and each of these phrases has a precisely defined meaning.
Codd, even if he's the father of normalization dosen't write a lot about that topic in his writtings.
The notions of super-key is mainly used in the context of normalization, which is an orthogonal topic to relational theory.
Following the process of normalization, building relations (or tables) to meet 2NF, 3NF, BCNF, 4NF, etc is not an obligation.
In relational theory we only need to respect 1NF.
The relational theory is not confined to Codd's writting. It has evolved and will still evolve.
The only key in relational theory is the candidate key. Or just "key". There is no notion of "primary" which indeed come from SQL.
June 30, 2011 at 12:11 pm
The relational theory is not confined to Codd's writting. It has evolved and will still evolve.
The only key in relational theory is the candidate key. Or just "key". There is no notion of "primary" which indeed come from SQL.
OK, here's a flippant comment so you can ignore this... when does a candidate key ever get promoted from candidate to the spot light? After all, even a Candidate for President is either elected or not... you don't ever stay a candidate indefinitely...
June 30, 2011 at 12:14 pm
venoym (6/30/2011)
The relational theory is not confined to Codd's writting. It has evolved and will still evolve.
The only key in relational theory is the candidate key. Or just "key". There is no notion of "primary" which indeed come from SQL.
OK, here's a flippant comment so you can ignore this... when does a candidate key ever get promoted from candidate to the spot light? After all, even a Candidate for President is either elected or not... you don't ever stay a candidate indefinitely...
You betcha! :hehe:
June 30, 2011 at 12:27 pm
Steven993 (6/30/2011)
The only key in relational theory is the candidate key. Or just "key". There is no notion of "primary" which indeed come from SQL.
Are you ok? Is this statement a joke?
It makes no sense to the educated. A candidate key is part of the superkey so how can the only key be the candidate key?
This quote comes straight from the current "evolution" of relational theory.
In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that
1.the relation does not have two distinct tuples (i.e. rows or records in common database language) with the same values for these attributes (which means that the set of attributes is a superkey)
2.there is no proper subset of these attributes for which (1) holds (which means that the set is minimal).
If you are going to comment on someones fact checking and research I recommend doing some of your own.
June 30, 2011 at 12:30 pm
What are you talking about ?
I was saying that the only kind of key is what is called candidate key. We should call candidate key, just key.
Of course a super-key is a superset of key.....
Don't forget I'm making a distinction between relational theory and normalization....
June 30, 2011 at 12:34 pm
Steven993 (6/30/2011)
What are you talking about ?I was saying that the only kind of key is what is called candidate key. We should call candidate key, just key.
Of course a super-key is a superset of key.....
Steven993 (6/30/2011)
The only key in relational theory is the candidate key. Or just "key". There is no notion of "primary" which indeed come from SQL.
It was your quote.. You tell us.
What I am talking about is that in SQL a Primary Key can be one or more columns and is based on the notion of the Super Key. So the name Primary Key started with SQL, but the notion it is based on came from past and present relational theory.
June 30, 2011 at 12:55 pm
OK, here's a flippant comment so you can ignore this... when does a candidate key ever get promoted from candidate to the spot light? After all, even a Candidate for President is either elected or not... you don't ever stay a candidate indefinitely...
A candidate key get promoted whenever the database designer alters the table with a PRIMARY KEY constraint. After that, the remaining candidates are now just "alternate keys"; just like alternates in a beauty pageant.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 30, 2011 at 1:14 pm
I never contain phone or address related columns in something like the primary customer table.
#1 An address or phone number isn't an attribute or measure of the customer; the telephone company assigns it to us for the duration of our contract, so we only have a relationship with it.
#2 Multiple customers may share the same address or phone number, and for many applications that's useful to know.
#3 Virtually nobody has only one phone number these days.
#4 There are usually additional contextual information like type, priority (primary, seconday, etc.), and also effective_from_date and effective_to_date that users need to know.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 69 total)
You must be logged in to reply to this topic. Login to reply