Re: Pizza Example

On Tue, 06 Apr 2004 10:02:20 -0400, Jerry Gitomer <jgitomer_at_erols.com> wrote:

>On Tue, 06 Apr 2004 13:23:44 +0100, Lemming wrote:
>
>> On Tue, 06 Apr 2004 01:00:26 -0400, Jerry Gitomer <jgitomer_at_erols.com>
>> 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.

Agreed, I made a similar point in another post in this thread. What I was responding to was the assertion that tehre was no risk associated with such a change.

>>>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.)

Agreed, although your initial assertion appeared to be that you could make such a change with no risk (which I read as "no impact").

>> 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.

Indeed, but I think you will find that in most systems, the whole length of the column data is retrieved in most cases. I have yet to find a system which routinely limits the data read to fit the fields into which it is read ... it's just too much work to code all those substr() functions for every select, particularly as when a column length changes it is likely you'll have to change the selects accordingly.

>> 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.

Not exactly. This was a very experienced and well liked DBA, who simply made a mistake. The only action taken was to make fun of him for a few days -- which had the desired effect, and was a lot cheaper than sending him out for training.

>>>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.

Noted. I was speaking as a practitioner, not as a theorist. Should I take it that discussion of practical issues are OT in this froup?

>> 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.

I believe we agree on this. As I say, I was responding to what I believed was your assertion that the size of a column could be increased without considering the impact.

Lemming

-- 
Curiosity *may* have killed Schrodinger's cat.