June 29, 2009 at 9:39 am
GSquared (6/29/2009)
2. You include columns like FirstNameID, LastNameID, and reference a Names table. If you need to find everyone named Fred, and the ID for Fred is 6, then you have to include Where FirstNameID = 6 in your query.
Such a design would drive crazy any developer. I had to work (not much, fortunately) on a DB designed this way and I can tell you that it makes things horribly difficult to code and understand. I also makes impossibile to import data from external sources. In my opinion it's not worth the effort, not to save some Gb in storage.
I think at some point you have to stop and accept a reasonable amount of denormalization. For your own mental sanity, at least.
-- Gianluca Sartori
June 29, 2009 at 9:43 am
I think at some point you have to stop and accept a reasonable amount of denormalization.
... if so it must be called. Is it the right word?
-- Gianluca Sartori
June 29, 2009 at 9:46 am
GSquared (6/29/2009)
GilaMonster (6/29/2009)
GSquared (6/29/2009)
What I think is funny is the people who think that "normalizing" is replacing all the data with ID FKs to other tables. Like, instead of having a person's name in a Customers table, you'd have columns like, "FirstNameID", "LastNameID", and joins to a Names table. I've actually had someone tell me that that's 3NF.Isn't that pretty much 6th normal form? Not one I'd use in anything other than the most extreme cases.
6NF is either Domain/Key NF or Temporal NF, per what I can find on it. 5NF just has to do with implicit/explicit join dependencies. 4NF is about non-trivial multi-valued dependencies.
Splitting names into a separate table isn't really part of any of the standard normal-forms, but it comes closest to 4NF, but only if you have a surrogate key on the table the names appear in (otherwise, name data is at least part of a candidate natural key).
It does not make the import of external data impossible, just a bit more difficult.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 29, 2009 at 9:51 am
GSquared (6/29/2009)
GilaMonster (6/29/2009)
Isn't that pretty much 6th normal form? Not one I'd use in anything other than the most extreme cases.6NF is either Domain/Key NF or Temporal NF, per what I can find on it.
I meant Temporal.
I admit there's little material I can find on it (don't own the book it was first referenced in), but from what I can tell it's breaking tables right down so that you can associate temporal data with each attribute. A table (ID Number, Surname, Phone Number, City) would go (to 6th) as something like this
(ID Number, DateChanged, Surname)
(ID Number, DateChanged, PhoneNumber)
(ID Number, DateChanged, City)
So that changes to each attribute can be separately recorded.
May have misunderstood some of it. I'm going mostly on wikipedia and some extracts of the book.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 10:04 am
Gianluca Sartori (6/29/2009)
GSquared (6/29/2009)
2. You include columns like FirstNameID, LastNameID, and reference a Names table. If you need to find everyone named Fred, and the ID for Fred is 6, then you have to include Where FirstNameID = 6 in your query.Such a design would drive crazy any developer. I had to work (not much, fortunately) on a DB designed this way and I can tell you that it makes things horribly difficult to code and understand. I also makes impossibile to import data from external sources. In my opinion it's not worth the effort, not to save some Gb in storage.
I think at some point you have to stop and accept a reasonable amount of denormalization. For your own mental sanity, at least.
Yes, it makes everything about it more difficult. That's why it's not really part of a standard Normal Form, in most cases.
Keeping names in a table where names are a candidate natural key or primary characteristic of the table, is not "denormalization" in the vast majority of cases.
Keep in mind that all normal forms are dependent on the definitions of the data for the entity that will be using it.
If, for example, you need to keep a record of all prior names a person has gone by, any nicknames or aliases the person uses, and so on, separating names from people might be necessary for the database.
For example, you need to keep track of me. You give me an ID number, let's say it's 5. Now, you need to keep track of these names, "GSquared", "Gus Gwynne", "Gus A Gwynne", "Richard Gwynne", "Richard A Gwynne", "Richard Gus Gwynne", "Richard Augustin Gwynne", and the all-important, "Hey You". (Richard Augustin Gwynne is my full name. I blame my parents. 🙂 )
How many of those names are you going to store in the People table in your "Annoying DBAs" database? Any more than one, and you're probably looking at a violation of 1NF. So you might have a Names table, and perhaps "ValidFrom" and "ValidTo" datetime columns, and a "Type" column (nickname, alias, pen name, screen name, etc.), and a "Position" column (indicating where each name can be in the sequence, which would have to include both numeric values and "first" and "last" values).
Now, that begs the question of, do you separate the elements of the names? If so, you need to have a table of which combinations are valid, or you end up with "Gus Augustin Richard Gwynne", which isn't a name I've ever gone by.
Will that kind of thing be more difficult to deal with programatically? Absolutely. Does it give you more data and more flexibility with the data? Also absolutely. It allows you to deal with names from cultures that don't do "first-middle-last". It also creates problems with "Is the middle name not there because we don't know it, or because he doesn't have one", which a column with a null or a blank space could answer quite nicely. Plus and minus.
Thus, it depends on the needs of the entity the database is meant to serve. Most just want to know my current "common name" (Gus Gwynne), and possibly a screen name/pen name (GSquared), and can adequately store that in a normalized table in columns with the relevant text in them. Government tax records, on the other hand ...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 29, 2009 at 10:10 am
GilaMonster (6/29/2009)
GSquared (6/29/2009)
GilaMonster (6/29/2009)
Isn't that pretty much 6th normal form? Not one I'd use in anything other than the most extreme cases.6NF is either Domain/Key NF or Temporal NF, per what I can find on it.
I meant Temporal.
I admit there's little material I can find on it (don't own the book it was first referenced in), but from what I can tell it's breaking tables right down so that you can associate temporal data with each attribute. A table (ID Number, Surname, Phone Number, City) would go (to 6th) as something like this
(ID Number, DateChanged, Surname)
(ID Number, DateChanged, PhoneNumber)
(ID Number, DateChanged, City)
So that changes to each attribute can be separately recorded.
May have misunderstood some of it. I'm going mostly on wikipedia and some extracts of the book.
That's pretty much it, so far as I understand.
I've used it in audit tables. Haven't found much use for it elsewhere.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 29, 2009 at 10:11 am
If I remember the Modeling class at the TDWI conference a couple of years ago, 6NF is used to eliminate nulls being stored in a database. Also results in a LOT of left outer joins to retrieve data. Imagine have separate tables for FirstName, MiddleName, LastName and needing to pull all that data back in a query.
June 29, 2009 at 10:12 am
I guess it says something about our geekiness that our "water cooler" conversation has to be policed periodically for overly technical matter. This bit about normal forms says something about every participant. Not sure exactly what, but it sure says something!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 29, 2009 at 10:15 am
Lynn Pettis (6/29/2009)
If I remember the Modeling class at the TDWI conference a couple of years ago, 6NF is used to eliminate nulls being stored in a database. Also results in a LOT of left outer joins to retrieve data. Imagine have separate tables for FirstName, MiddleName, LastName and needing to pull all that data back in a query.
Actually, a structure like that would probably end up encouraging violations of 1NF, since you do have people with two middle names. If it doesn't comply with lower forms, it's not the higher form, so such tables are more likely to be in 0NF (which doesn't officially exist, but defines more databases than all the others combined!).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 29, 2009 at 10:16 am
Alvin Ramard (6/29/2009)
It does not make the import of external data impossible, just a bit more difficult.
That's exactly what I had to do for that DB...
It's possibile, but I coded tons of SQL to import a single table.
Nothing is impossibile, but something ends up being a real pain.
-- Gianluca Sartori
June 29, 2009 at 10:18 am
GSquared (6/29/2009)
I guess it says something about our geekiness that our "water cooler" conversation has to be policed periodically for overly technical matter. This bit about normal forms says something about every participant. Not sure exactly what, but it sure says something!
It happens. I've had hallway meetings turn into technical discussions, even when they started off with "Did you see the latest Transformer movie this weekend?"
June 29, 2009 at 10:19 am
GSquared (6/29/2009)
Lynn Pettis (6/29/2009)
If I remember the Modeling class at the TDWI conference a couple of years ago, 6NF is used to eliminate nulls being stored in a database. Also results in a LOT of left outer joins to retrieve data. Imagine have separate tables for FirstName, MiddleName, LastName and needing to pull all that data back in a query.Actually, a structure like that would probably end up encouraging violations of 1NF, since you do have people with two middle names. If it doesn't comply with lower forms, it's not the higher form, so such tables are more likely to be in 0NF (which doesn't officially exist, but defines more databases than all the others combined!).
That is probably when you would end up with FirstMiddleName, SecondMiddleName, etc.
June 29, 2009 at 10:20 am
GSquared (6/29/2009)
I guess it says something about our geekiness that our "water cooler" conversation has to be policed periodically for overly technical matter. This bit about normal forms says something about every participant. Not sure exactly what, but it sure says something!
It's starting to get a bit messy here. The middle name table could end up with multiple rows per person. I guess you would need a column to indicate the order for multiple middle names. The same would also apply to last names.
hmmm, if there's a column for the order of the middle names then why not store first and middle names in the same table?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 29, 2009 at 10:23 am
Lynn Pettis (6/29/2009)
If I remember the Modeling class at the TDWI conference a couple of years ago, 6NF is used to eliminate nulls being stored in a database.
There's actually a strict interpretation of 1NF that some people favour that states that a table that allows nulls in any column does not adhere to first normal form.
The justification is that a table in 1NF must have the same number of attributes for all rows, and allowing nulls is allowing different rows to have different numbers of attributes.
It's not a view I favour, but there's people how do. In fact, I recall a panel discussion at PASS last year titled 'Much ado about nothing' where several people debated null for an hour and didn't come to an agreement. The associated thread on the MS private newsgroups went on for months.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 10:23 am
Gianluca Sartori (6/29/2009)
Alvin Ramard (6/29/2009)
It does not make the import of external data impossible, just a bit more difficult.That's exactly what I had to do for that DB...
It's possibile, but I coded tons of SQL to import a single table.
Nothing is impossibile, but something ends up being a real pain.
Been there, done that. You'll get no argument from me.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 6,166 through 6,180 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply