Normalization really does matter

Incompetence...it's seriously starting to *** me off

Brian H. Madsen - .Net Powered by Caffeine — Thu, 28 Sep 2006 05:27:59 GMT

Now, i'm not going to mention any names here, because somebody may get offended. but - i've been

re: Normalization really does matter

Brian Madsen — Thu, 21 Sep 2006 05:17:58 GMT

Yep - he's officially gone underground... can't raise him on messenger or email :(

re: Normalization really does matter

Brian Madsen — Wed, 13 Sep 2006 16:00:25 GMT

Bill seems to have dissapeared off the face of the planet again......

re: Normalization really does matter

Brian Madsen — Fri, 08 Sep 2006 15:35:02 GMT

Just going to post Greg's blog link..

http://blogs.sqlserver.org.au/blogs/Greg_Linwood/

if there's a guy out there that i have more respect for regarding SQL Server i'd be very very surprised....

re: Normalization really does matter

Brian Madsen — Fri, 08 Sep 2006 15:31:59 GMT

Here in Australia we have a gentleman known as Greg Linwood..

now 5-6 years ago i had no idea who this guy was..but after having read his responses in an SQL Server newsgroup i've seriously gotten some respect for him...not only does he know what he's talkign about, but he also steps outside of the box when he deals with technical issues.

He's going to present in Perth in a week or so..once for my group, Perth SQL Server User Group and once for Perth .Net Community of Practice..both are going to deal with indexes and how to deal with them..one of the sessions are geared towards developers (durr) and the other towards database administrators - needless to say, i'm going to attend both sessions as he's going to touch on some of the things in the industry that i'm extremely keen to know more about.

He recently dispelled the myth about Clustered Indexes and i think i'll ask his permission to post some of his answers on my own blog as he's absolutely right..

Anyways, Greg is one of those guys that steps over the boundaries and takes a stance on technology which clearly indicates that he doesn't compromise when it comes to SQL Server...

seriously wish you could attend these two session Bill..i think you would have gotten a lot out of it.

re: Normalization really does matter

Brian Madsen — Fri, 08 Sep 2006 15:16:46 GMT

I think the answer to your question is simple...

and here i'm most likely going to be shot down in flames by some utter brain-lacking moron (pardon the language here, but this is one aspect of development i'm VERY fanatic about)..

here we go:

The industry boomed, and brought a ton of "developers" out of the bush who's only really in the industry for a paycheck..they don't have the same passion for development that this industry really deserves, so they come into work every day, put in 8hrs in their chair, and thinks that's the end of the story..they don't take pride in having a working application implemented while knowing that the engine is going to keep on ticking and ticking and ticking..

I see it like this..developers are divide into two main groups..Aston Martin and Hyundai..they both do the same thing - bring you from point A to point B, yet you can tell you've just paid $5.000 for the car because you find nearly any excuse you can to catch the bus.

Real life story here:

my senior developer and myself, where told to take a look at two of our old legacy applications at work...one is a very very important MS Access form application, backed by an SQL Server..the second is another MS Access form application, also backed by SQL Server..

now, first we found that there was absolutely no documentation on either of the applications (both are developed in-house), so we took time to have a talk to the "manager of the DB department" who built parts of the first application..we asked him to send us the DB schemas of the DB first so we could see what type of data we were going to be dealing with..he did, and we both sat shocked for nearly 20 minutes at how badly this database was structure...first off, second pet-hate of mine is some idiot using MS_ID as the primary key for every single table..yes, you read that right..each and every primary key in that database had the same name...it did contain relationships so data integrity should be ok..well, to our shock and horror it wasn't..the application had been built years ago by a technician of sorts who bought an MS Access book..so can't blame that gentleman..but, the "manager" we spoke to is a VERY VERY VERY known figure in the SAP space..yet, he had continued to patch this application in the last year or so by using the exact same methodology as the technician initially did.

So who here is the culprit that i should burn at the stake?? the technician or the "manager"??

i think the answer here is simple..the "manager" should be quartered and drowned by pushing pages of a relational database book down his throat..

and that was when i realised that not everybody working in this industry is as passionate about what we do as i was..can we say reality check!!

re: Normalization really does matter

William — Fri, 08 Sep 2006 14:50:50 GMT

Then it's good to hear I'm not alone. I've worked with so many people who come up with utterly lame reasons not to normalize and in every case, they have never studied relational theory to make even a modest argument against it.

I think there's two big issues. One is normalization. The other is sloppy implementation. using reserved words as field names isn't a normalization issue. But it still sucks almost as much. Slapping 50 indices on a 20 field table isn't a normalization issue but it does present overwhelming issues in most cases. On and on.

So I ask you Brian - why is it that in spite of overwhelming evidence for and virtually no evidence against - good design is still the bain of many? And as far as violators being shot, the first country to make it a crime to violate good db design practices will be my new home the day after the law passes.

re: Normalization really does matter

Brian Madsen — Fri, 08 Sep 2006 14:38:04 GMT

"it then uses that tables foreign key in other tables to link..."

should have been...

"it then uses that table's PRIMARY KEY in other tables to link..."

re: Normalization really does matter

Brian Madsen — Fri, 08 Sep 2006 14:35:55 GMT

Bill,

that is probably one of the best posts you've made in a long time...as you may recall, my pet-hate/pet-love is developers and their sometimes absolute lack of knowledge on databases and the rules governing them.

At work, i have the pleasure of maintaining the database server (yes i'm not a DBA, but my department is a bunch of top notch guys) which contains probably one of the worst written database in histor.

It knows nothing about:

1) Data integrity, not one single foreign key in the entire database schema

2) normalisation, there are tables with an absurd amount of columns in them...

ex. a Customer table.

usually holds primary key(s), immediate information such as username and passwords, email addresses...but we go further here, it keeps absolutely everything about the client in that table...it then uses that tables foreign key in other tables to link them together (such as products and services the client has, invoices, logs etc) but in those tables there is not relationship set up to the "Customer" table. it just fully trusts that there will never be an error inserting data, or that some data will be corrupted...no, this pleasant application (a billing and provisioning system called Platypus for ISPs..owned by TuCows now) trusts it's data explicitly.

overall you could overcome this with modern technogies..say object oriented databases or by using strongly typed dataset ala ADO.Net..but HA..no, this application is written in Visual FoxPro..to make things worse, it allows you to extend the database by adding "custom tables" to the schema, via the application UI itself..again, no relationships or normalisation here..and to make matters worse, we have had more than 300 tables in this schema at some points.

whoever wrote this appliation should be shot..or even better, have a funnel down their throat and a corner office desk pushed through.

Anyways, now with more than 20.000 customers in the database performance is absolutely shocking...to the extent that corruptions are occuring on almost a weekly basis, which of course has to be fixed by C'est Moi..

Since taking over the department and getting a very depe insight into the applications and systems spread out through the company, this database backed application should never have seen the light of day.

i would give my right testicle for a solution i could replace this piece of crap with..it is the ultimate proof that there should be some divine governing body to regulate developers..

</rant>

Normalization really does matter

It's Way Too Early For This — Thu, 07 Sep 2006 22:57:37 GMT

Some good points in here about normalization. And he does make the point that sometimes it is best to