From: jfr@RedBrick.COM ()
Subject: Re: Data Base design pet peeve
Organization: Red Brick Systems, Los Gatos, CA
Date: Mon, 28 Sep 1992 00:18:27 GMT

In article <1992Sep25.132241.29976@glv.uucp> andersen@reality (Robert Andersen) writes:
>In article <66577@cup.portal.com> DonD@cup.portal.com (Don Robert DeCosta)  
>writes:
>> ... the presenter then
>> decides the item is a 2 character string limited to numerals, he says
>> you make this decision because this data item will never be manipulated
>> as numeric, you don't SUM customer_id, you don't AVG customer_id, etc.
>> 
>> Why
>> would I want to use 5 bytes to store "30000" when I could use 2 bytes
>> to store it as 30000? And why do I want to force my CPU to do a string
>> compare loop when looking for a key item when it could be doing a simple
>> single instruction integer compare? And, Hey! This is SQL, it's relational,
>> so my customer_id is gonna show up in every ORDERS record wasting another
>> 3 bytes and again in that foreign index wasting another 3 bytes.
>> 
>> Like I said this has come up before, this simple, "If you don't perform
>> math on it, make it alpha" logic... I think it's totally wrong!
>
>I agree with your pet peeve.  Using that kind of logic is going to slow down  
>performance or make it difficult to change if you have to keep increasing  
>the number of records stored. What's his explanation when asked what happens  
>when you have more records that will fit into a 2 character field?  Using an  
>integer will save space and allow your application to grow without having  
>to change the attribute type, from char(2) to char(10).  But of course you  
>could use the alpha-numeric way, it would only cost you more space/time.
 (Above edited for space/time trade off :-)
 
This notion of "planning for the future" as a justification for choosing
one type of data representation (possibly the wrong one) over another
always elude me.  If the guy KNOWS his database isn't going to grow
over 99 customers, maybe he should use a ONE BYTE integer instead of
a TWO BYTE one, hey?  Wouldn't this save even more space?  And if he
has made a mistake and needs more than 32767 (or 64K if you support
unsigned ints), then shouldn't he use a FOUR BYTE integer from the
start?  Why is TWO BYTEs inherently better than any other choice the
user might decide to make?  The real problem here is that most databases
are REALLY CRUDDY at rapid adaptation to changing database requirements.

To prove we haven't come as far as we might have thought:

Anecdote time:

In 1968, I took my first real programming job at Chase Manhattan bank as
a programmer in the Time Deposits department.  We were supporting a major
system (hee hee, at least for those times it was major) of 17 tape files
and 72 COBOL programs on a Burroughs 5500 (great machine, for ITS time :-)
In 1969, the onslaught of inflation due to the Vietnam War caused interest
rates to stir and then rise, quite rapidly.  In the summer of 1969, Chase
issued its first CD for 10% interest rate.  The problem was that the entire
system was written with the interest rates defined (in both files and 
programs) as 9V999 (for the COBOL-impaired among you, this is sort of like
%4.3f but not really because the decimal doesn't get stored cause the V
means its implied... never mind :-)  There was, of course, none of the
proverbial "slack" space there should have been in the files (after all,
we were trying to save space, right? :-) so we had to dump all the files
(this system had over 1,000,000 records on tape - 800bpi tape :-) and
rebuild the files and the programs and test them, etc, all within 3 months.
And we were "real" programmers, with coding forms and key punch operators,
none of this wussy terminal crap with on-line debuggers for us, nosirree!
This was the first of many many 20-hour a day, 7-day a week projects I have
been on in my career.  It did, in fact, turn out successful.  But what I
would have given for a database which simply allowed me to redefine the
storage capacity of the interest rate field, say "GO" and have it all
fixed for me.

Anecdote 10-4

The point of the above is that, IMHO, you should do database and data
design based on what you need today and for the foreseeable future and
based on the "most appropriate" representation for the data.  And the
system and tools you use should make it simple to adapt to the future. 
If they don't, you may be using the wrong system or tools.

BTW, as another point, the argument is made that using an integer is
"faster" than using a character string.  But what about the effort
involved in converting integers to character strings for external
representation?  So maybe searching on a string is slightly longer
(although I don't necessarily agree with this thesis and it certainly
depends on the processor) but converting to and from internal format
will be much longer for the integer than for the string.  The main point
is that you should be MUCH less concerned with the space/time tradeoffs
at least in the early stages of design, than you are in what is RIGHT.

Jon Rosen
jfr@redbrick.com
-----------------------------------------------------------------
"Where is the wisdom? Lost in the knowledge.
  Where is the knowledge? Lost in the information." -- T.S. Eliot
"Where is the information? Lost in the data.
  Where is the data? Lost in the #@$%?!& database." -- Joe Celko
-----------------------------------------------------------------

Newsgroups: comp.databases