June 22, 2009 at 8:58 am
Grant Fritchey (6/19/2009)
Matt Miller (6/18/2009)
I like your questions Grant. I usually just add in one more, it's actually usually my first (fail is and it's time to call it quits)."What is normalization and why is it important?"
...amazing how many crickets you hear after that one....
I don't think I'd ask that on a screening question. I get WAY too many people that really and truly can't explain, don't know, don't understand, even after you point it out, that there is a difference between a block and a deadlock. It makes me more than a bit nuts.
A deadlock is where two blocks can't make nice (they block each other), and SQL Server kills the one that is taking up less resources? Hope that's correct. 😀 Still learning.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
June 22, 2009 at 9:04 am
Brandie Tarvin (6/22/2009)
"Why, no, Sir. We don't be needin' no real-ational database type tool. Just gimmie Lotus 1-2-3 or that thar x-el spreadsheet and I kin give you the best durn database you ever did see. Without usin' that silly normal stuff everybody's bin talkin' 'bout."@=)
Hyuck, hyuck. Why fo' shur, darlin'. Yestaday I cud na spel "DBA"... todays I r one. I gots a reel purdy DB... jest one ol' big tabel wit no inndekses or nuttin' to clog up da vinyl on dat mosheen. Sae... ya wanna see my twoth? 😀
--Jeff Moden
June 22, 2009 at 9:07 am
Normalization has a lot of value, and is used daily by (I hope) most people working with databases. To what extent you normalize might be debatable, but you ought to have normalized your tables to some degree.
This is not some ancient technique. You gain efficiencies in storage and data quality. It seems like a pain for the developer that has to write the joins, but that's short sighted. You're missing the fact that every mistake a user makes, and you make, and they will happen, means poor data quality and problems down the road.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 22, 2009 at 9:10 am
Michael Valentine Jones (6/22/2009)
Sorry if my irony wasn't thick enough to detect, but just for the record I do think databases should be normalized. :blush:
I recently asked a developer why the tables in his design had no primary keys, no constraints of any kind, no indexes, all columns were nullable, and dates were stored in varchar(8) instead of datetime. His reply was that my concerns were entirely theoretical, with no value in the "real world".
Sorry i did not mean to single you out, my comments were really meant for the developer who wrote the db i am currently working on , though I highly doubt he uses this site 🙂
June 22, 2009 at 9:14 am
Gaby Abed (6/22/2009)
A deadlock is where two blocks can't make nice (they block each other), and SQL Server kills the one that is taking up less resources?
That's one of the more creative descriptions of a deadlock that I've heard in quite some time. I like it.
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 22, 2009 at 9:41 am
GilaMonster (6/22/2009)
Gaby Abed (6/22/2009)
A deadlock is where two blocks can't make nice (they block each other), and SQL Server kills the one that is taking up less resources?That's one of the more creative descriptions of a deadlock that I've heard in quite some time. I like it.
Thanks, sometimes it helps to "humanize" some of the more abstract or confusing SQL concepts. Still working on log shipping. 🙂
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
June 22, 2009 at 10:11 am
Heh... "Log shipping" = "hauling lumber" 😛
--Jeff Moden
June 22, 2009 at 10:41 am
Jeff Moden (6/22/2009)
Heh... "Log shipping" = "hauling lumber" 😛
Oh, the agony! Oh, the pain!
Oh, I wonder how many more of these we can come up with... We could post a list as an article. @=)
Replication = How baby databases get created.
Database Mirrors = What geeks hang in their closets.
June 22, 2009 at 10:43 am
Jeff Moden (6/22/2009)
Brandie Tarvin (6/22/2009)
"Why, no, Sir. We don't be needin' no real-ational database type tool. Just gimmie Lotus 1-2-3 or that thar x-el spreadsheet and I kin give you the best durn database you ever did see. Without usin' that silly normal stuff everybody's bin talkin' 'bout."@=)
Hyuck, hyuck. Why fo' shur, darlin'. Yestaday I cud na spel "DBA"... todays I r one. I gots a reel purdy DB... jest one ol' big tabel wit no inndekses or nuttin' to clog up da vinyl on dat mosheen. Sae... ya wanna see my twoth? 😀
Is that thar SQL book in yer pocket or r u just happy ta see me? @=)
June 22, 2009 at 10:56 am
Brandie Tarvin (6/22/2009)
Jeff Moden (6/22/2009)
Heh... "Log shipping" = "hauling lumber" 😛Oh, the agony! Oh, the pain!
Oh, I wonder how many more of these we can come up with... We could post a list as an article. @=)
Replication = How baby databases get created.
Database Mirrors = What geeks hang in their closets.
Heh... ok...
"Transactional Replication" = That which occurs in a DB for a brothel.
"Merge Replication" = Occurs between consenting databases.
😛
--Jeff Moden
June 22, 2009 at 11:17 am
ROFLOL
Mirroring - Not really replication with or without consent - just cloning yourself.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 22, 2009 at 11:24 am
Page - 80's version of texting (who carries a pager these days?)
However, if the page is torn, then i don't know how to answer Gails question - would it then be when the page comes in two notifications breaking the phone number into two?
Database Security - Oxymoron? Shouldn't all developers have SA to all environments?
SQL Server Agent - A Super Spy for MI6
Transformation Services - Plastic Surgery for Replication gone awry - or Government agency for the Autobots.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 22, 2009 at 11:54 am
Jeff Moden (6/22/2009)
Brandie Tarvin (6/22/2009)
"Why, no, Sir. We don't be needin' no real-ational database type tool. Just gimmie Lotus 1-2-3 or that thar x-el spreadsheet and I kin give you the best durn database you ever did see. Without usin' that silly normal stuff everybody's bin talkin' 'bout."@=)
Hyuck, hyuck. Why fo' shur, darlin'. Yestaday I cud na spel "DBA"... todays I r one. I gots a reel purdy DB... jest one ol' big tabel wit no inndekses or nuttin' to clog up da vinyl on dat mosheen. Sae... ya wanna see my twoth? 😀
The sad part is - I've actually had the very conversation Brandie was talking about. I actually talked a few people out of their databases, because fro the torture they were giong to put it through, they'd be better off spending their 20G on a couple hundred Excel licenses.
It's scary these days how often that kind of crazy logic comes up. "Dont keep {insert anything RDBMS'es are good at} in the database server - they're too hard to deal with", while in the same breath telling me that good developers should be able to read pretty much any kind of code to some degree of proficiency......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 22, 2009 at 1:29 pm
Michael Valentine Jones (6/22/2009)
steveb (6/22/2009)
Jeff Moden (6/22/2009)
jeffrey yao (6/20/2009)
Michael Valentine Jones (6/18/2009)
Matt Miller (6/18/2009)
..."What is normalization and why is it important?"...
Just say that “normalization” is theoretical nonsense that so-called gurus like Date and Celko throw around to try to impress people, and it has no practical value in the real world.
I totally agree that "normalization" is theoretical nonsense in most of DBA work. Knowing or not knowing this does not hurt anything. It is similar like "what is a data model?", "what is a process model?" and "how do you integrate them? " etc
I love it when people believe that... makes finding jobs to fix bad databases so much easier. 😛
I agree, it has kept me gainfully employed for a number of years fixing databases where the original developer considerd normalisation to be of no real value, and from the sounds of it there will be plenty of more work to come:)
Sorry if my irony wasn't thick enough to detect, but just for the record I do think databases should be normalized. :blush:
I recently asked a developer why the tables in his design had no primary keys, no constraints of any kind, no indexes, all columns were nullable, and dates were stored in varchar(8) instead of datetime. His reply was that my concerns were entirely theoretical, with no value in the "real world".
I think "no primary keys, no constraints of any kind, no indexes, all columns were nullable, and dates were stored in varchar(8) instead of datetime." really has little to do with normalization. It is more about physical data model implementation for performance and integrity purpose.
It is a fair statement that database (or the data model inside the database) should be normalized. But so far I have not seen a database that is fully normalized to 3NF, but this may be due to various reasons. As once a dedicated data modeler (full-time for 1+ year), I find it is ridiculously difficult (or even impossible) to come up with a data model that can break the 1NF / 2NF / 3NF (even assuming I have NO concept of normalization) while STILL meeting business scalability and flexibility requirements. When a data model comes out, the model will need to go through two tests, one is user case test (for basic business requirement) and another is pseudo-code test (for developer coding practice). I have to admit breaking 3NF is easier and sometimes deceptive, but with robust test cases, it is easy to point out the in-efficiency of the model. To sum up, 1NF/2NF/3NF is just some theory there, even if you do not understand / hear of it, you will follow it instinctively. In another word, 1NF/2NF/3NF (esp. 1NF) is more or less a common sense and if you do not stick to it, your design will not pass the regular user cases.
With all this said, I believe asking about normalization in an interview or "challenge/impress" people by playing around this "normalization" is not suitable.
June 22, 2009 at 1:57 pm
jeffrey yao (6/22/2009)
I think "no primary keys, no constraints of any kind, no indexes, all columns were nullable, and dates were stored in varchar(8) instead of datetime." really has little to do with normalization. It is more about physical data model implementation for performance and integrity purpose...
If you follow the following definition of 1st normal form, you would have a hard time claiming that a table without a primary or candidate key is really in 1st normal form, since there is no assurance that it conforms to item 3 in this list.
1. There's no top-to-bottom ordering to the rows.
2. There's no left-to-right ordering to the columns.
3. There are no duplicate rows.
4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
5. All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].
—Chris Date, What First Normal Form Really Means
In the example that I gave, the developer showed that he had absolutely no understanding of the issues, so his claim that my concerns were only theoretical was just pure BS to deflect criticism of his complete failure to do professional level work.
Viewing 15 posts - 46 through 60 (of 112 total)
You must be logged in to reply to this topic. Login to reply