1) No logical model
Obvious isn’t it? But I can almost guarantee that everyone has started a DB project without one at some stage. You are lucky if you see an ERD and should buy a Lotto ticket if you see a Functional Dependency analysis.
As an example, my current project’s schema can be broken into 7 functional areas. I produced ERDs and FDs for 5 of these areas but ignored the other 2. Despite changing requirements, the “planned” 5 areas remain almost untouched, while the “unplanned” schemas have undergone countless re-writes. By the way, the 5 “planned” areas make up 90% of the business model.
I wasted my time and the client’s because I got lazy…
2) Physical Contamination:
While preparing your logical model, you start thinking too hard on what it will look like in the DBMS. It is a subtle issue that can distort your model.
This is how it affected my current project... Confusion and Clarity
3) Using NULL
Look out for the flames! Besides the relational heresy, NULL is hardly ever modeled at the logical level, but is jammed into the physical database as an afterthought.
Do you ever hear middle tier programmers complaining that C#/VB/java etc. cannot assign NULL to an integer or decimal (value types) in the middle/UI tier? Oh wait… you will when the database starts spitting out NULLs at them…
Programmer: “What will I display to the user?”
Dodgy DBA: “NULL”
Programmer: “But the UI control is limited to integers only!”
Dodgy DBA: “I don’t know… how about 0?”
4) No Domains/types
How many times do you see people declare columns as simply the base type with no additional constraints?
e.g. NumberOfSiblings INT
I don’t know many people who have minus 65 children….
Nothing quite like receiving the wrong results at the speed of light is there. If you have to, you MUST tell the database about it. And in my experience it is bloody hard to do that with current SQL DBMSs…
A good example of this was at my last project. A central query required a 38 table JOIN. Response time from the DBMS (in this case DB2) was (I though at least) an impressive 220ms average. “Not good enough” was the call from the powers that be. A big meeting took place and after almost 6 hours of utter crap, it was decided that all this data would be stored redundantly in another table just for this query. The resultant query was almost instantaneous. The big wigs where happy but the DBAs where not. Why? Because we had to add triggers to keep it in sync and then run verification procedures every night to ensure correctness.
You’re probably thinking, “Suck it up Dave! That’s your job!” And good for you! But now there is an extra level of management (not cheap), and surprisingly (to the managers at least) the DBMS has to work harder.
Manager: “Why are the clock cycles up 12%?”
DBA: “Because I have to verify your busted arse table!”
Manager: “Do you have to?”
DBA: “Do you remember when we made the front page because we sent the wrong information to some of our clients?”
It was actually page 3, but front page sounds better…
6) Performance obsession
Almost identical to the “Physcial Contamination” issue.
You know the type of people I am talking about.
“A string SEEK takes 2 milliseconds longer than an integer, so damn the model and stuff this meaningless number in there…at least it goes fast”
Yeah right… As fast as a lemming to a cliff.
I am fully aware of the lemming myth. Hail Disney!
7) No default values
Not mandatory of course, but makes the DBMS more responsive to change. As an example, a changing requirement might require an additional column to be added to a table. Without a default, you WILL have to re-write all your DML code and most probably client side code as well.
8) Identity madness
Excessive use of artificial/surrogate keys turns your lovely RM based logical model effectively into a pointer based cluster f***.
9) Relying on the application for correctness
One of the primary rules in data management is to trust no one or thing to provide correctness EXCEPT the database. Just because current DBMSs suck so badly at even simple rules doesn’t excuse you from passing responsibility to the application.
10) Relying on Stored Procedures for correctness
Stored procedures are a mixed blessing. Great for security and client interaction, but can make you lazy when implementing business rules. If it can be done at DDL time.. DO IT!
| posted on Monday, September 12, 2005 12:35 PM