[AccessD] [Spam] Re: Why Change Field Size/was Change Field Size

John Colby jwcolby at ColbyConsulting.com
Mon Dec 5 23:36:41 CST 2005


>As shocking as it is, John's example, below, of an in-house application
with an on-site developer suffering a few minutes of down time because the
company changed its own clearly defined business rules doesn't qualify as a
horror story.  It's just a matter of holding people who define a
specification accountable and a bit of amusement for a developer.  You don't
get to say 'I told you so' if you pre-empt them at every turn.

In fact, the rules were the client's client's rule.  I am not an onsite
developer, in fact I get to the client's site once every few weeks.  I
cannot hold anyone's feet to the fire except my own.  The limit existed to
begin with because I ported an existing table and didn't carefully look at
each and every text field for silly limitations.  My client is under quite
rigorous guidelines from THEIR CLIENT as to how long they have to get an
insurance claim entered, from the time they get their hands on it.  This is
not a "little" nor is it an "amusing" problem, at least not to them, nor to
me.  To my client, this is indeed a horror story.  But more importantly it
is a silly, useless waste of everyone's time.

I have a framework.  The framework handles the NotInList, offering to open a
form for entering the new data.  If the form is opened, the form seeks the
proper record, and then places the entered value in the default value
property of the control bound to the field in the source table that the
original combo was displaying data from, so that as the user begins entering
data the data that they already entered in the combo (not in list) does not
have to be re-entered.  Because the size of the field that the control in
the opened form was too small, the form displayed #error as it opened (after
I placed data in its Default Value property).  It was quite confusing to
everyone, and I had to trace through my code to discover why this was
happening.  Access did not throw an error, just displayed #error in the
control.

As for the total size limit, yea it has to be respected, but it has to be
respected whether it is 20 fields with a 255 size limit or  255 fields with
a 20 character limit.  Just arbitrarily limiting the field size does not
guarantee that you do not run into the page size limit, all it does is raise
the probability that you will run into valid data that cannot be entered.
If the PAGE LIMIT were 512 bytes you can be damned sure I would think twice
about my "always use 255" decision, but it isn't.

There are damned few fields that you can accurately predict how long the
data will be and NEVER get an issue with data exceeding whatever your best
guess was.  So now what is your suggestion?  Run mathematical calculations
on the probability of the occurrence for every field you design?  Hmmm...
What is the probability that Address1 will exceed 40 characters.  Let me
spend an hour researching Address1 fields from every database I can find,
discover the maximum length of that field, add 50% to that figure.  On to
Address1, then City...  

I think not.

If I am going to make Address1 50 because it is never going to be more than
that, should I do so?  Why?  To prevent some twit from trying to enter the
Boy Scout's oath in the Address1 field?  The database I am discussing had
phone number masks.  The client swore that no one would live outside the US.
Most importantly, THEY BELIEVED THAT.  But of course it wasn't true.  And
the masks came back out.  Work to set them up, work to take them back out.
And I ASKED THAT QUESTION SPECIFICALLY because they wanted the masks.  Just
because the customer says that a business rule is true doesn't mean it is,
nor does it mean that the rule won't change tomorrow, in fact you can COUNT
on just about any rule changing eventually.  You walk a fine line whatever
you do, enforcing it here has advantages, but it has disadvantages.
Enforcing it over there has advantages, but it has disadvantages.  I have
been on all sides of this problem and I have decided (for me) that
artificial and arbitrary limitations in field size, for an Access database
creates more problems, more often, that it prevents problems.

I use 255 because I have never had an issue with wide open fields (yes, my
users have entered trash in there but they can do that in arbitrarily
limited fields as well) and I have run into DOZENS of problems with
arbitrary limits preventing real data from going in.  It is quite that
simple.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jürgen Welz
Sent: Monday, December 05, 2005 11:18 PM
To: accessd at databaseadvisors.com
Subject: [Spam] Re: [AccessD] Why Change Field Size/was Change Field Size

I had a peek at the Archive under the topic:

[AccessD] On DB Bloat, Bad DB Design, and various


Fri May 21 16:53:55 CDT 2004 quoting Drew Wutka:

'I have never been burned by the page file size.  In fact, I completely
forgot that the limit even existed, until it popped up on the list a few
weeks ago'

Fri May 21 15:31:41 CDT 2004 quoting Arthur Fuller:

'Having never heard this before, I created an Access table with 9 text
(255) fields and ...'

Fri May 21 19:01:10 CDT 2004 quoting John W. Colby:

I think I will go off right now and test what error I see when I
intentionally try and save a record with more than 2K record size.

Although I too generally use the maximum allowable field size for many text
fields, there is a limit imposed by the database format that must be
respected.  I suppose if that size limit were 511 or 1023 that most of the
other contributors to this thread would use that as the limit.  Just how
sensible is it to allow a phone number field, or a State or Country name
field to contain 255 characters.  How much sense does data make and how
logically can you set sensible query criteria and interpret data of text
type reasonably expected to contain 10 characters where a user enters 250
like the phone number example I gave earlier.  I don't see any harm in using
an input mask that restricts a field to 10 characters yet has a size limit
of 255 characters.  The next question is, if you set the input mask at the
table design level, you have to punt the users to make changes.  If you do
it at the form level and the field is in a dozen various forms, do you do it
in code each time, or do you set an input mask at the control level and
copy/paste the same control so you don't mess up?

A person who can code himself out of any corner who competently addresses
data rules at the form level, provided they are aware of them, can get away
with setting the size to the maximum.  Employing memo fields for all text
fields always created further difficulties in specific scenarios such as
union queries.  But when dispensing advice at this list, there are readers
who are not well served by the bald statement that some reputable and
respected developers always sets the limit at 255 characters for text
fields.  For this reason, you need to add the caveats and potential
consequences of following such an arbitrary practice.

As shocking as it is, John's example, below, of an in-house application with
an on-site developer suffering a few minutes of down time because the
company changed its own clearly defined business rules doesn't qualify as a
horror story.  It's just a matter of holding people who define a
specification accountable and a bit of amusement for a developer.  You don't
get to say 'I told you so' if you pre-empt them at every turn.

If you want a real fun example, just think of a couple of characters and
y2k.  I'm not about to start feaking about y10k though I'm sure some of you
would allow 255 character years because you're so certain that your
applications will be around that long.


Ciao
J|rgen Welz
Edmonton, Alberta
jwelz at hotmail.com




More information about the AccessD mailing list