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

Charlotte Foust cfoust at infostatsystems.com
Mon May 24 13:02:17 CDT 2004


Single table query on a table with all 255-char text fields returned a "query too complex" error.

Charlotte Foust

-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] 
Sent: Monday, May 24, 2004 9:10 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


Actually, I would say that relying on Jet to do data validation is lazy programming, not the other way around.

Lets say you were storing State abbreviations.  So you limit your field size to 2.  Why limit the field size, when you can just force data entry through a combo box (or even use a lookup field...LOL, sorry, couldn't resist). When the US takes over the entire world, we may have to go to 3 letter abbreviations.  When that happens <evilgrin>, if you have limited your field, you now have 2 changes to make, instead of 1.

Once again, I've been burned quite a few times by other people's field size limitations, and I have NEVER been burned by fields being set to 255.  Just out of curiousity, how did the 255 character fields bite you in a query?

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust
Sent: Monday, May 24, 2004 11:51 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


Not a valid argument on the phone numbers, Drew.  International phone numbers are still not going to be more than about 15 characters, so why make the field 255?  I've been bitten by 255 character fields in queries, so I tend to "limit" the field size to something reasonable for its contents.  No one could ever remember a 255 digit phone number, so it isn't likely that a full size field would ever be needed for that.  It's just lazy programming to default to 255. <VBG>

Charlotte Foust

-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] 
Sent: Friday, May 21, 2004 1:54 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


I think we are talking apples and oranges here.  Yes, the page file size needs to be taken into consideration.  I set all of my text fields to 255. I do this because I don't want to be backed into a wall, because I set a size limit that prevents a user from entering what they need to enter.  If I provide a phone number field, and set it to 10 (area code and phone number), sure, I am 'limiting' the client.  However, what happens when they want to put in an international number.  Or if the US decides to move to 8 digit phone numbers.  Who knows, there are all sorts of reasons that the field size may change.  Now, if I have some sort of logic checking data integrity, that would have to be changed, but if I don't, by having the field size set to 10, I am limiting the users at the table level, to a point where they cannot do their job.  If I have it set to 255, I am 99.99999% they would never put 255 characters into that field, but they may put in 11, or 12, etc.  

Oh well, this really isnt' something I feel like arguing about.  I see your point Jürgen, but this is really a case of who has been burned and how.  I have been burned over and over by previous developers putting such limitations into their databases.  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.  So that is why I set my default text field size to 255.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jürgen Welz
Sent: Friday, May 21, 2004 3:27 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


If you assume you have an autonumber field, a couple date fields, a few 
foreign key fields together with 10 text fields and you place no restriction

on the text field size, you will find that there are verbose users who are 
unable to save records because the non text fields use up some 50 to 100 
bytes leaving space for only about 1950 characters in the record for ten 
text fields.  Allowing 255 characters in ten text fields is fundamentally 
flawed with such a table.  If you have 20 text fields, allowing 100 
characters per field will result in record save failure.  Statistically, 
there will be few situations where an entry person would enter the maximum 
amount of data in all 10 or20 text fields but the design does not prevent a 
record save failure unless some restriction is placed on a few of the 
fields.  You could trap the error and offer to truncate fields or dump 
copies into a memo field but it is safer for people learning at a community 
college level to restrict text field length so that the total of all fields 
cannot exceed record size limits.

In cases where a user needs to enter more text than some realistic limit, I 
find it useful to provide a notes memo field.  I have also used a system 
where overflow data was placed in a separate table on a field by field basis

and reconsituted in the display field.  Back in the old days of A97 before 
SR1, memo fields were particularly buggy and I built a system that stored 
text in a table consisting of fields identifying the source table, the 
autonumber record ID the field and the overflow data.  A similar system can 
be used to all text fields with 300 characters while preserving the ability 
to Union tables (can't with memo) and to sort on (the first part anyway) of 
memo fields (not that it makes sense to sort on them but...)

If you invite users to enter 255 characters in a phone number field or a 
postal code field, I can guarantee that they will.  And when you sort or 
filter on that kind of data, the results aren't promising.


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





>From: DWUTKA at marlow.com
>
>Oh no, haven't forgotten, but if the limit is 255, that doesn't
>actually affect the 2k character limit.  Actually putting 255 
>characters in will, but not the actual field size definition.  However, 
>if you limit a field to 35 characters, because you think that's all a 
>user will need, and one day they need to put in 36 characters....NOW 
>you are preventing the record from being
>saved.  I have found very few instances where there was an ironclad reason
>to set the maximum field size to something other then 255.
>
>Drew
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jürgen Welz
>Sent: Friday, May 21, 2004 1:02 PM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various
>
>
>"Unnecessary" size restrictions on fields ensure that a record can be 
>saved.
>
>   If you have more than 8 text fields at 255 characters per field,
>Access will refuse to save the record.  Have you forgotten the the 2k 
>character limit on record size in mdb files?
>
>Ciao
>Jürgen Welz
>Edmonton, Alberta
>jwelz at hotmail.com
>
>
>
>
>
> >From: DWUTKA at marlow.com
> >
> >Actually I think that talking about Access training/experience is a
> >valid topic.
> >
> >Recently, I have helped a friend with his Access 'homework' from his
> >MIS course.  I worked cheap (for beer <grin>), but it just kills me 
> >what they are having students do.  The 'instructions' for the 
> >assignments has them naming tables with spaces, setting unnecessary 
> >size limitations for text fields, etc.  Just plain ugly.  In fact one 
> >instruction was so goofy, I completely bypassed what they were trying 
> >to do, and wrote some VBA to
>have
> >the same result.
> >
> >It is just flat out amazing that the world moves as it does. <grin>
> >
> >Drew

_________________________________________________________________
MSN Premium helps eliminate e-mail viruses. Get 2 months FREE*  
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=htt
p://hotmail.com/enca&HL=Market_MSNIS_Taglines

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list