Re: Pizza Example

From: Jerry Gitomer <jgitomer_at_erols.com>
Date: Tue, 06 Apr 2004 01:00:26 -0400
Message-Id: <pan.2004.04.06.05.00.25.384317@erols.com>

On Mon, 05 Apr 2004 11:19:56 -0500, Dawn M. Wolthuis wrote:

        [snip]

> I understand now from Paul's response that the reason folks like to
> restrict the size of fields is not for performance reasons, but because
> they want the database, not just software applications, to kick out data
> that is clearly wrong. I suspect that in many cases, by the time data
> reaches the DBMS a software developer has already found a reason to apply
> constraint logic (coded outside of the database) to the transactions
> because they needed to for the GUI or to provide an immediate response to
> a web services transaction, but this locks down the database further so if
> a programmer doesn't catch something, the database will.
>
> It does seem to me that it might make more sense, then, for these lengths
> to give warnings in many cases, rather than be fixed constraints if they
> are checking for the likelihood of good data, but I don't know if there is
> a clean way to do that.
>

This runs counter to the DBA culture. As a DBA my primary responsibility is the integrity of the contents of the database. Giving a warning implies that I store bad data that doesn't satisfy my validation rules. I would rather bounce any questionable data back to those who know it and have the ability to correct it. (If they need to increase the size of the column I can do that without taking the database down, so this is not a biggie and won't interfere with any other use of the database.)

> Otherwise, allowing the database to store every data element as variable
> length unless there is a clear business reason not to, but ensuring that
> all points of entry of data -- GUI or web service, for example -- apply
> constraint logic with warnings in cases where unlikely data is entered
> makes more sense to me.
>

There are rules for maximum length for many data items. For example, the maximum length of the standard US Postal Service name of a city in the US is 14 characters. The maximum length of an international telephone number is either 14 or 15 characters. The length of a US Postal Code is zip+4 or 9 integer digits. In these and similar cases if the size of the data exceeds the database specification it is wrong and should be rejected out of hand. When it comes to the length of names, for example, I agree with your position that a relatively long variable length column be used.

        [snip]

>> > Constraining the database to only store up to a particular length
>> > means that the database needs to be changed if a new

> valid

>> > value for an attribute arises that is longer than what was
>> > anticipated.
>>
>> How terrifying!  More likely of course, the user will come up with a
>> suitably abbreviated name.  We are all familiar with such abbreviated
>> names from till receipts, credit card statements etc.

>
> Yes, exactly the state of data I have seen. Instead of the easy, but
> not completely without risk, task of altering a table, the user simply
> figures out how to work around the constraints. I think that is a bit
> of a shame, but I guess I can see where the cost of both end-users and
> software developers figuring out how to live with the initial
> constraints for the life of a software application rather than "fixing"
> them could be considered part of the cost of the software. Hopefully
> neither the user or the developer will go to extremes in this regard and
> will request the required changes when needed (but we have all seen
> cases where they have, right?)
>

Whoa! I can alter a table and increase the width of a column without taking the database down or interfering with the users of the database. There is no risk in increasing the width of a column. If you are working with a DBA who refuses to make necessary changes or a DBMS that won't allow such changes get rid of the offender.

        [snip]

>>
>> What it is worth is a LOT, since computers work better with data
>> structured according to rules rather than "unstructured" or "natural"
>> data.  Who cares if it seems "natural" to you?  If you are a database
>> designer, you better learn to see beyond the "natural" if you are going
>> to be useful, and if you are an end-user accessing the database via an
>> application you can be protected from needing to understand the
>> relational way of doing it.

>
> But if there is a structure and set of rules that align better with the
> natural structure of language, then we could possibly have a cost
> savings over the life of software (and that is what the anecdotal
> evidence suggests is happening, but I have nothing to prove it). I am
> NOT at all opposed to structure. I am alsol pro-constraints and would
> like to see the constraints that are important for data integrity be
> applied at the proper points in an application -- that might mean both
> in a GUI and when the data are stored.
>

When it comes to OLTP systems the rule is KISS! The reason is to reduce development, maintenance, and usage (training, data entry, data lookup and processing) costs over the life of the system. Over time the experience is that rigidly structured data minimizes these costs.

> What I have seen with RDBMS's is that the size constraint is placed on
> everything while more important constraints
> related to the allowable values are not as often applied, especially
> when it is a "yellow flag" type of data value for an attribute rather
> than obviously not permitted. I'll put this in a separate thread at some
> point (but have several other questions prior to that one).
>

I prefer that the "more important constraints" be handled by the application programs rather than incorporated into the RDBMS. This is because the application programs are specified and written by developers and analysts who are working with the users and have first hand knowledge of the business rules while the database is cared for by the DBAs who are concerned with the efficiency and effectiveness of the database in meeting the needs of many different applications.

HTH
Jerry Received on Tue Apr 06 2004 - 00:00:26 CDT