"Frank Swarbrick" <Frank.Swarbrick@[EMAIL PROTECTED]
> wrote in message
news:485FC580.6F0F.0085.0@[EMAIL PROTECTED]
> >>> On 6/23/2008 at 10:16 AM, in message
> <485F77F7.6F0F.0085.0@[EMAIL PROTECTED]
>,
> Frank Swarbrick<Frank.Swarbrick@[EMAIL PROTECTED]
> wrote:
> > Thanks to all who responded. I new feel quite confidant in shooting
this
> > idea down.
> > :-)
>
> Interesting. Here's a page my co-worker is using to justify his
position:
> http://www.dbforums.com/showthread.php?t=1619660
I don't know where to start. If I were to discuss all his points in
detail
I would end up writing a book on bad database design and how to avoid it.
And that book wouldn't be any better than several books Joe Celko has
already written.
But I'll give a brief summary:
*** One easy to find table with all valid values for given fields. This
means a maintenance screen can be added so these values can be kept up to
date in the future.
The table may be easy to find, but the relevant values for data
validation
are just as hard to find, even harder to find IMO. You have just as many
types of codes in OTLT as you would have in separate reference tables and
you have to know just as much to restrict your view of the OTLT as you
would
have to know to reference a separate table.
There is no need to add a maintenance screen for reference data. If the
data is to be maintained manually (and that's a big if), then a tool
built
for that purpose is provided by the DBMS maker. That tool has a near zero
learning curve, and requires no programming. If worst comes to worst,
you
can even use MS Access, set up a table link to each reference table, and
let Access make the grids or forms for you.
*** No need to create a new table for new lookup values. This means the
database schema doesn't change, it means no new code needs to be written
and
it means you don't need to schedule a weekend release for your code.
This is possibly the worst reason of them all. What he's saying is that
the
true structure of the data can be mutable while the database schema
remains
static. It is precisely the fact that the database schema and the true
structure of the data are in conformance to each other that makes the
database schema a valuable tool for managing and using the data. A
database
schema can be used as a poor man's data dictionary. In fact, Oracle
refers
to its metadata as "the Oracle Dictionary".
Anybody who doesn't understand the usefulness of a data dictionary should
stay away from databases.
As far as new code goes, no new code will have to be written for new
table
maintenance, as I said before. New code will have to be written to use
the
new code type. But that's true regardless of whether you create a new
table
or add to an existing OTLT. And as far as adding a new table breaking
existing code goes, it just doesn't happen in the real world. In over
ten
years of database work, I've never seen any code that was dependent on
the
non existence of a certain table for its correct functioning.
*** A single stored proc can be provided to test if a value is valid
before
adding it to your param table. This proc should be the only method of
adding
data to the database.
Code validation can be enforced using referential integrity constraints.
A
single stored proc can still be built, even if that tables are kept
separate. I'll describe how to do that elsewhere.
*** Fewer tables in a database usually means less code meaning less bugs.
Just plain false. Fewer tables in a database generally means more code
meaning more bugs.
*** All the features you provide in your OTLT will be available for all
lookup values. So if you provide a method to order values in HTML forms
then
this facility will be available for all lookups. You could provide default
values or transformations that might turn UNKNOWN values to NULL.
It's easy to propagate standard features to a new table. It's a moot
point.
*** I can understand that they don't feel comfortable with storing the
allowed values 1,2,3,4 in a varchar field within our OTLT. Would it help
if
we regard these values not as being integers but simply as valid values
that
are stored in our valid values table.
It's isn't just the form of the data that I'm not comfortable with. The
CHAR(2) code "CO" can mean "Continental Airlines" or "Colorado" depending
on the context. These two codes are from different domains, even though
they have the exact same form.
My answers are just the tip of the iceberg. What I really want your
friend
to learn is that database experts aren't total fools. Most of us have
been
around real live databases, and we know what can go wrong from direct
experience, not just ivory tower dreaming. This is true regardless of
whether we learned from our own mistakes, or from the mistakes of others.
|
