RELATIONALIZE. PLEEEEAAASE!

>> Celko, please come here and beat my colleagues to death. You get overtim
e
pay if you beat the bosses too. I might even help. <<
Tell the colleagues that Celko says this stinks. Which you already
know. But if they hear from someone who wears a suit and lives more
than 100 miles away, they will suddenly believe it. I do not know why,
but it is true.
Your boss needs to hire high-priced consultants, like me! Bosses like
to hire high-priced consultants because it is easier and faster than
learning to do things right in the first place. They may or may not
actually use the report from the high-priced consultant, of course.
But the important thing is that the check clears.
I understand avoiding NULLs, but I do not understand designing a
numeric encoding which does not have special values if you can have
exceptions. There is no room for expansion, the '?' makes no sense,
etc. I have a chapter on the design of encoding schemes in SQL
PROGRAMMING STYLE.
For example, in the ICD disease codes 000.000 means "undiagnosed" and
999.999 means "diagnosed, but unknown and you are going to die". Two
very different kinds of missing values!
I am going to guess that they wanted something more like this:
CREATE TABLE Foobar
(tip_code CHAR(3) NOT NULL PRIMARY KEY
CHECK (tip_code LIKE '[0-9][0-9][0-9]'), -- guess
tip_group ICHAR(1) DEFAULT '0' NOT NULL
CHECK (tip_group LIKE '[0123]' ), -- wild guess
fatigue_dispo CHAR(1) NOT NULL
CHECK (fatigue_dispo IN (..)), -- no idea
hw_desc VARCHAR(100) DEFAULT 'N/A' NOT NULL,
sw_desc VARCHAR(100) DEFAULT 'N/A' NOT NULL,
prt_desc VARCHAR(100) DEFAULT 'N/A' NOT NULL,
st_desc VARCHAR(100) DEFAULT 'N/A' NOT NULL);
It needs a key, it needs constraints. It is insane to put numerics in
tip_group when a zero can be used as a default value. I am also
assuming that they do not need to describe things with Chinese
characters, so they do not need NVARCHAR(n). This guess loads
print-able defaults they seem to want.
If you want to be super-safe, clean out the spaces and uppercase things
in the descriptions
CHECK (hw_desc = UPPER (LTRIM(RTRIM(REPLACE (hw_desc, ' ', '
'))))
Or put a clean-up routine in a TRIGGER, if youcan afford it. .> Tell the colleagues that Celko says this stinks. Which you already
> know. But if they hear from someone who wears a suit and lives more
> than 100 miles away, they will suddenly believe it. I do not know why,
> but it is true.
You may or may not believe this, but the boss is now giggling at the
corporate nonsense, but that's not enough to change policy or design.

> Your boss needs to hire high-priced consultants, like me! Bosses like
> to hire high-priced consultants because it is easier and faster than
> learning to do things right in the first place. They may or may not
> actually use the report from the high-priced consultant, of course.
> But the important thing is that the check clears.
I wholeheartedly agree. I've already done gigs like that when I used to be
a contractor. Before MS Access had replication, I wrote a two-way updatable
replication system between Access 2.0 & an HP 3000 Mainframe. Daily updates
to the tune of 65 MB over a SERIAL link at 19Kbaud. YIKES! And the COBOL
dude I worked with was always saying stuff like, "Why is your program so
slow?!?" and I was like, "Tony, your program runs on a mainframe. Mine
runs on a 386SX with 8 MB of RAM and pulls data over your stupid serial
link!" I don't think he ever got it.
OH! and they had wonderful things like a CHAR(30) column [well, the HP
COBOL equivalent of X30] which was a series of 'Y' and 'N' that represented
boolean values that I had to program checkboxes with. People that design
stuff like that should just be summarily SHOT.

> For example, in the ICD disease codes 000.000 means "undiagnosed" and
> 999.999 means "diagnosed, but unknown and you are going to die". Two
> very different kinds of missing values!
Special values like that have been the BANE of every system I have ever
worked on. Example: At my current job, they have this thing they have
called the "Master Product List" which is clustered on an Identity Key, yet
they have values for Keys from 995-999 hard-written in the database. At
every team meeting I keep bringing up the future issue of what happens once
we get enough different imported records from the clients that the Identity
Key collides with these hard values. They poo-poo the idea every time.
Sooner or later this entire system is going to have a Y2K-style problem, and
I'm going to say "I told ya so".

> It needs a key, it needs constraints. It is insane to put numerics in
> tip_group when a zero can be used as a default value. I am also
> assuming that they do not need to describe things with Chinese
> characters, so they do not need NVARCHAR(n). This guess loads
> print-able defaults they seem to want.
HAHA! You should see the damn phone numbers!
Working here is sort of like living inside a Pink Floyd video or a Salvador
Dali or MC Escher painting.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave Mustane|||On Wed, 21 Sep 2005 13:21:29 -0400, Mike Labosh wrote:

>HAHA! You should see the damn phone numbers!
Hi Mike,
Hey, that's just great! I can imagine the conversation next time
something important breaks at just the moment that Murphy's laws
predict.
"Do you know how to fix it?"
"No, no idea"
"Okay, then let's call Mike on the emergancy cell phone. Can you just
look up the number in the phones table for me?"
"Sure - just dial () ?-?"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)