Posted by --CELKO-- on 10/30/06 17:02

1) The silly, redundant “” prefixes are in violation of both common
sense and ISO-11179 rules. Hey, we only have one data structure in
SQL!

2) Never put audit data in the table that is being audited. This is
the same principle as not putting the log file on the same drive as the
database. Revision dates never go into a table in the schema, but are
kept externally to it. Under SOX rules in the US, you can go to jail
or be fined.

3) Why did you fill your data dictionary with vague names that beg to
be defined? Prefix of what? Body of what?

4) Eligibility has only one “l”, so your names are a bitch to read.
I really aware of that error; I have a tattoo that lets the world know
I am O-positive and have a Penicillin “Alergy” – Really !

5) I see you immediately jump to procedure calls with
“proc_qualificationdetail”. Again the redundant prefix and poor
name are in involution of basic software engineering. Tell me what the
procedures does, not what the parameters are. That means a
<verb><object> name, probably something like
“Determine_Eligibility()” instead.

Of course since this is a logical function, you probably should not use
a procedure or function at all. It ought to be in the WHERE clause of
the UPDATE statement.

>> I create the table variable : <<

Because in the file system you are creating, all data must be
materialized to be persisted. In SQL, we have VIEWs for data that is
computed over and over as the schema changes. Also this is proprietary
and good programmers write to standards, not to dialect.

I gather that this routine is supposed to assign prizes to entries in a
promotion (much easier to understand that than just Tables A, B and C,
isn’t it?). Without any more information, all anyone can do is guess
at the DDL, but here is my shot in a skeleton schema

CREATE TABLE Entries
(entry_id INTEGER NOT NULL PRIMARY KEY,
..);

CREATE TABLE Promotions
(promo_id INTEGER NOT NULL,
prize_id INTEGER NOT NULL,
PRIMARY KEY (promo_id, prize_id),
..);

CREATE VIEW Winners (entry_id, promo_id, prize_id)
SELECT entry_id, promo_id, prize_id
FROM Entries AS E, Promotions AS P
WHERE << assignment predicate >>

I am assuming that prizes are a part of promotion, not separate
entities that can exist alone. Having done something like this
before, the << assignment predicate >> is a lot of hand-waving if the
rules are complex. But it can be done (cannot have won a prize in the
last 30 days, not an employee of Radio Station WXYZ, etc.) with careful
use of CASE expressions, you can do a decision table (Google "Logic
Gem") and be sure that you have a complete rules set.

[Back to original message]