Re: Pizza Example

From: Jerry Gitomer <>
Date: Tue, 06 Apr 2004 10:02:20 -0400
Message-Id: <>

On Tue, 06 Apr 2004 13:23:44 +0100, Lemming wrote:

> On Tue, 06 Apr 2004 01:00:26 -0400, Jerry Gitomer <>
> wrote:
>>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.
> Just because you can, doesn't necessarily mean that you should.
I agree with you, but if you look back at the full context of what was said you will see that I was responding to the mistaken impression that once a structure was defined for a database that it was cast in concrete and/or that making any such changes would be a major task.

>>There is no

risk in increasing the width of a column.
> There are many risks associated with increasing the size of a column,
> some of which have been mentioned elsewhere in the thread, but for
> example:
> Programs may have been written assuming that a column is of a particular
> size, and so have allocated only enough working storage to accommodate
> that size. If the column size increases, those programs will begin to
> fail once the "old" size is exceeded. If those programs run as part of
> a batch suite, it may be necessary to re-run the suite from the last
> save step. If your month end suite takes 24 hours to run, and it falls
> over an hour from the start, then on rerun falls over again after two
> hours, then again after four, etc. all because the DBA increased the
> length of the "flavour" column by 1 character so a user could enter
> "Pineapple and strawberry cream with a hint of vanilla, cointreau and
> gooseberries" there are going to be a lot of questions the next day.

If your production application programs aren't tested after changes are made to the database you deserve what you get. A quick review of the applications should reveal which, if any, programs and functions should be looked at when a database change is made. (You should have a "Where Used" list for all of your data items so that this becomes a trivial exercise.)

> Reports may have been created reliant
on the column being a particular
> size. Changing the column size may mess up the report to such an extent
> that it is illegible or misleading.
> If the column is used to populate a data item which is part of a
> structured record picked up by another program, or a data packet
> transmitted to an external system, the receiver of that record or packet
> will likely not be able to cope with the new size.

The presentation format of the data is independent of the storage format. If I so choose I can store a data item as an 80 character column, but extract only the first 50 columns for reporting purposes or to forward to a foreign system.

t> For a
 real-world example which should be familiar to most people,  > recall
> Y2K, where a simple change from two-digits to four-digits to represent
> the year required an enormous development effort. If a user had come to
> you, the DBA, to request that you change all the year fields to four
> digits, would you have done it? Just because you can, doesn't mean you
> should.

The problem wasn't the way the data was stored in the databases and files, but that many applications used bad assumptions in performing date arithmetic. Incidentally in the databases I have been using for commercial work dates are stored in an internal format and I would have shown the user how to use a format mask to get the desired/required presentation format.

> In a system I worked on a couple of years ago, a DBA was asked
by a user
> to add an index to a column with very low cardinality (it was a flag
> which contained the values 0 and 1 only). It seemed a harmless change,
> so he did it. A queries which had been taking a few milliseconds began
> taking around 5 minutes to complete. This query was executed, once per
> call, on an overnight data-collection system, connecting to 12,000
> remote terminals via dialup. Calls normally took around 20 seconds to
> complete, with a 10-second "no data" timeout. Needless to say, we didn't
> collect any data until that was found and fixed.

I should hope that the DBA was chastised and, depending on his/her experience, sent out for some training or fired.

>>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.
> Because of the risks, it is necessary to perform impact analysis for
> even the most apparently trivial of changes to the database structure.
> Before a DBA makes such changes, he should have to satisfy himself
> firstly that the change is really necessary, and secondly that adequate
> impact analysis has been done. Where the impact analysis shows that
> changes are required to programs, reports, etc. which make use of the
> data he must also be satisfied that these changes are being/have been
> completed, and the change to the column length will take place at the
> same time new versions of programs/reports are implemented. This is why
> most well-run organisations have change-control measures in place: Users
> request changes, explain the reason for them, experts from a number of
> areas perform impact analysis, the change is either approved or denied
> and development resources are allocated to the changes required to the
> rest of the system.

You are right, but that is the difference between a theory group where we can ignore the realities of maintaining large databases and application systems and the nitty-gritty practical issues involved in supporting fail-safe systems that meet the needs of real enterprises on a 24/7 basis.

> This isn't confined to relational databases, it's a "feature"
of all
> database systems which have to interface with the outside world, where
> "outside world" includes programs, reports, GUIs, datacomms, etc.
> Lemming

My own feelings are that the database should be limited to serving as the repository for the enterprises data, that everything possible should be done to assure that only valid data is stored in the database, that the integrity of the data is protected by adequate backups and disaster planning, and that everything related to the database be tested before it is put into place in the live applications and database.

I also feel that it is the responsiblity of the application developers to validate everything they can before attempting to store it in the databaswe and to extract and format according to their processing and presentation requirements when extracting the data from the database.

Jerry Received on Tue Apr 06 2004 - 16:02:20 CEST

Original text of this message