June 29, 2009 at 7:27 am
Hey, Gail. With Gus it is now up to 5 individuals you now know that know "The Key, the Whole Key, and NOTHING but The Key!"
June 29, 2009 at 7:27 am
GSquared (6/29/2009)
GilaMonster (6/28/2009)
Lynn Pettis (6/27/2009)
Okay, you were between 6 and 10 when I learned that in University. Not exactly when or which school I was in when I had my first database class.😀
You're the first person I know (outside of my 2nd year CS class) that also knew that mnemonic.
I know the mnemonic, can quote it and all that. So I guess you know at least two.
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. Same guy said that datetime being replaced with "DateID" and "TimeID" was 5NF.
From an old SQL 2000 Admin book from SAMS publishing:
"3rd normal form - no attributes depend on other nonkey attributes" I think they say something like if there's a primary key but an attribute does not depend on it, move it to another table and link with a surrogate key. So if you have an EmployeeID, ManagerID, and ManagerName in an employee table, ManagerName does not depend on the primary key, EmployeeID, so move ManagerID and ManagerName to a different table and link with ManagerID in the employee table. That being said, if there are only twenty employees, what's the point?
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
June 29, 2009 at 8:02 am
Grant Fritchey (6/29/2009)
BTW, below my Codd Oath, the other saying I have posted, again, no one appreciates:The Rules: They may be stupid, arbitrary and irritating, but god help you if you break them.
I used to have the 'Lack of planning' saying on the front of my PC.
We had no cubes, just reasonably high dividers between desks. You had to stand up to see the person sitting opposite. That's not too bad, but it's very noisy. Far worse is that company's new floor layout. Rows of desks that remind me of school, no dividers between people, no board at the back of the desk and the seats were close enough that if you stretched you could hit both of your neighbours at the same time.
Fortunately I left before they moved to the new building.
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 8:09 am
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.
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 8:19 am
GilaMonster (6/29/2009)
We had no cubes, just reasonably high dividers between desks. You had to stand up to see the person sitting opposite. That's not too bad, but it's very noisy. Far worse is that company's new floor layout. Rows of desks that remind me of school, no dividers between people, no board at the back of the desk and the seats were close enough that if you stretched you could hit both of your neighbours at the same time.
Looks like a picture of my office. I must admit that seats are a bit larger than that, but the rest looks exactly as my office.
Fortunately I work with silent an busy people.
-- Gianluca Sartori
June 29, 2009 at 8:27 am
GilaMonster (6/28/2009)
Lynn Pettis (6/27/2009)
Okay, you were between 6 and 10 when I learned that in University. Not exactly when or which school I was in when I had my first database class.😀
You're the first person I know (outside of my 2nd year CS class) that also knew that mnemonic.
Sign me up for knowing it. I even used it in a recent job interview when the topic got to normalization. (I ended up being offered the job, but I turned it down... no desire to live in the DC area...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 29, 2009 at 8:28 am
That reminds me, I have photos of my old office
http://picasaweb.google.com/GilaMonster.za/Misc#
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 8:31 am
Gianluca Sartori (6/29/2009)
lmu92 (6/29/2009)
Referring to the original purpose of The Thread:Any consultant around ready for some charity work?
This one is perfect for Wayne 😀
:rolleyes:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 29, 2009 at 8:32 am
Gianluca Sartori (6/29/2009)
GilaMonster (6/29/2009)
We had no cubes, just reasonably high dividers between desks. You had to stand up to see the person sitting opposite. That's not too bad, but it's very noisy. Far worse is that company's new floor layout. Rows of desks that remind me of school, no dividers between people, no board at the back of the desk and the seats were close enough that if you stretched you could hit both of your neighbours at the same time.Looks like a picture of my office. I must admit that seats are a bit larger than that, but the rest looks exactly as my office.
Fortunately I work with silent an busy people.
Seems I can be happy about my situation. One room with two people - my trainee and I.
June 29, 2009 at 8:33 am
Gianluca Sartori (6/29/2009)
lmu92 (6/29/2009)
I'll do it, assuming you're covering travel and accommodation costs as well. 😀I'm still offering 5 dollars, better than nothing.
Anyway better than "I want (you) to cut my grass"!!:-P
LOL
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 29, 2009 at 8:47 am
GilaMonster (6/29/2009)
Grant Fritchey (6/29/2009)
I used to have the 'Lack of planning' saying on the front of my PC.
I use to have MYOB
Mind Your Own Business to remind me not to interrupt other peoples discussions, even if I know the answer :blush:
Far away is close at hand in the images of elsewhere.
Anon.
June 29, 2009 at 8:55 am
Regarding the Nth Normal Form, and I guess on a more philosophical level, is it true that the more normalized a database is, the less fragmentation occurs so the more efficient the indexing? If a table or set of tables is comprised of keys that help join with the tables underneath with actual data, wouldn't the majority of fragmentation that results be because of deletions of records, rather than anything else, assuming all indexed primary and foreign keys are in some sort of sequential order (so no use of newid() in this case)?
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
June 29, 2009 at 9:13 am
Gaby Abed (6/29/2009)
Regarding the Nth Normal Form, and I guess on a more philosophical level, is it true that the more normalized a database is, the less fragmentation occurs
I'd say no. At least not unless you're looking at the kind of 'normalisation' that Gus was talking about.
Normalisation makes no requirements about what the primary key is. I could have a 3 column composite primary key on varchar columns and still be in 3rd (or 4th) normal form. Plus, the primary key doesn't have to be the clustered index (it only is by default), so regardless of what the pk is comprised of, I could have the cluster on an incrementing column, or on something that fragments terribly
It may, in general, be said that a well-normalised table is easier to index, but that'll be more because it's (usually) narrower, but that won't hold true in every case.
wouldn't the majority of fragmentation that results be because of deletions of records, rather than anything else
Deletion does not cause fragmentation. Deletion can result in pages that are partially empty.
The definition of logical fragmentation is the % of pages that are 'out of order', ie a page having a lower page ID than the page before it in the index chain. Both insertions (on non-incremental key columns) and updates (that widen the row) can cause page splits which may result in fragmentation. Deletes, because they do not add data and hence do not cause page splits don't cause fragmentation.
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 9:25 am
Gaby Abed (6/29/2009)
Regarding the Nth Normal Form, and I guess on a more philosophical level, is it true that the more normalized a database is, the less fragmentation occurs so the more efficient the indexing? If a table or set of tables is comprised of keys that help join with the tables underneath with actual data, wouldn't the majority of fragmentation that results be because of deletions of records, rather than anything else, assuming all indexed primary and foreign keys are in some sort of sequential order (so no use of newid() in this case)?
Not completely.
If, for example, you had a Customers table, and you need name data in it, here are two scenarios:
1. You include columns like FirstName, LastName, and so on. If you need to find everyone named Fred, you have to use a Where clause that includes that. If that's common, you have an index on the FirstName field. It gets fragmented, because it's doubtful that you insert people into the table in alphabetical order by first name.
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. Since, again, you aren't inserting in name-sequence, if you have an index on FirstNameID, it will get fragmented by inserts/updates. Add to that the cost of a join, and you probably have worse performance than the first one, but that will depend on use.
The real advantages and purposes of normalization are less storage, which also means less IO, and more efficient updates.
In the cases above, an integer takes less space than any reasonably sized varchar, so storing "Fred" once and 6 dozens/hundreds/thousands of times, saves a potentially significant amount of drive space. That also means it probably takes less buffer (RAM), and can be read from the disk faster. Thus it might be faster, but the join math might compensate for that. You'd have to test. On the other hand, if you want to change a record in Customers from Fred to Freddy, it's significantly more complex, since you have to find the record ID for "Freddy", or insert into Names if there isn't such a record, and then use that to update the Customers table.
Also in the above case, if you go with NameFirst, NameLast (or FirstName, LastName), and so on, it will probably take more drive space, and more RAM and IO because of that, but if you want to change one person from Fred to Freddy, it's just an update. There isn't join math if you need to know the name that goes with a CustomerID, but there might be more IO needs, so again you'd have to test for overall speed.
It's not really about index fragmentation. It's about those factors: space (storage and RAM), IO, join math, update logic.
Of course, having a Names table, you also need to take reasonable efforts to manage the data in it, because you otherwise will end up with multiple entries. You might make the name the key, and have an ID on it that's not the PK, but is what FKs reference (which merely requires a unique index, not a PK).
Another advantage, off-topic, to a Names table, is that you can have a NameEquivalences table that records, by whatever means, the fact that "Johnathon" is the same as "John", "Johnny", "Jack", and so on. You can also do so with names columns in the Customers table, but it's easier with a Names table.
- 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 9:37 am
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).
- 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
Viewing 15 posts - 6,151 through 6,165 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply