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

Scott Marcus marcus at tsstech.com
Tue May 25 12:17:50 CDT 2004


Drew,

I thought it was odd that your indexer would just crash (or maybe I'm not understanding) because of size limits.

On another note about error handling:

What happens when you walk out the door (not the applications you wrote) and there is no error handling?

You're right, you'll probably get flamed big time for admitting this, so I'll leave you alone on it.

Scott Marcus
TSS Technologies, Inc.
marcus at tsstech.com
(513) 772-7000

 -----Original Message-----
From: 	accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]  On Behalf Of DWUTKA at marlow.com
Sent:	Tuesday, May 25, 2004 1:07 PM
To:	accessd at databaseadvisors.com
Subject:	RE: [AccessD] On DB Bloat, Bad DB Design, and various

It was an unexpected anamoly.

I don't want to take this on a completely different tangent, but to answer
your question, I'll have to get into my 'error handling' techniques a bit.
Quite frankly, I rarely use error handling.  Before I get completely blasted
off the List, let me explain.  I don't use error handling very often,
because a large majority of my systems reside 'in house'.  I intentionally
use error handling when I need it for 'expected errors', ie, if I want to
see if something is in a collection, I try to 'grab' it, if it errors, I
know it doesn't exist.  Also, when writing VB Services, I use complete error
handling (creating VB Services without errorhandling on every process can be
detrimental to your systems).  I also include error handling in my VB .dll's
that interact with ASP (most of the time).

Now, to finish up, before I am flamed, I only do this for projects I have in
my 'sphere' of influence.  I want to actually code around issues that arise.
However, if something is going out the door, ie leaving my sphere of
influence, then I do use error handling.

With that explained, the 'AccessD Indexer' is a VB Service.  Thus it is
error handled at every step.  However, all I have the error handling doing,
is reporting to the Event Handler (so messages show up in the Application
Events log).  I flip flop a bit on VB services, depending on what I need
them to do.  If it's a 'mission critical' system, where it needs to be
running no matter what, then I create 'never surrender' error handling.  It
logs it, tries to deal with anything it can, then keeps chugging.  On those
systems, I usually have it email me when an error occurs, so I don't let a
program just run away.  If it's not mission critical, or if it must
following a step by step process, then I use fall over handling, where if an
error occurs, it just stops, and usually emails me.  The Indexer was this
way.  I actually didn't change any of the code, but went back into the index
dbs, and changed the field size to 255.  

Did that answer your question?

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Scott Marcus
Sent: Tuesday, May 25, 2004 6:21 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


< Well back in September, the code ran into a string of alpha characters
over 100 characters long.  Bang, indexing service stopped.

Why wouldn't you write code to handle this exception?

Scott Marcus
TSS Technologies, Inc.
marcus at tsstech.com
(513) 772-7000

 -----Original Message-----
From: 	accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]  On Behalf Of
DWUTKA at marlow.com
Sent:	Monday, May 24, 2004 3:37 PM
To:	accessd at databaseadvisors.com
Subject:	RE: [AccessD] On DB Bloat, Bad DB Design, and various

"if your chief reason for choosing ridiculously long field sizes"

Ridiculously long?  It takes no more room in the db.  How is 255
ridiculously long?  Just curious.  Do you set all of your fields to 50, by
default?

An example using the actual list, when I built my archive indexer, I happen
to set the 'word' size limit to 100 characters.  I figured when could you
possibly ever get a word over 100 characters long?  My code chunks words at
any non-alphanumeric characters.  Well back in September, the code ran into
a string of alpha characters over 100 characters long.  Bang, indexing
service stopped.  Grrrrr.....

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Brett Barabash
Sent: Monday, May 24, 2004 1:36 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


Interesting perspective...

One assumption that I made in my comments was that this discussion was
focused around Access application development.  If you set a field size to
35 characters and try to type in more in a bound Access form, it won't let
you.  No code, no "unsaved" records.  It just restricts the length.  Period.

If you are writing an ASP page, or a VB front-end (which I do quite
frequently), then I can understand your argument.  Since the presentation
layer is disconnected from the data, you lose the data-bound validation that
Access inherently provides.  However, I would still contend that the
database schema is the best place to store that info.  One could easily
write code in their app to enforce the database field size rules.  Should
their needs change, open the table designer and update the schema.  No
redesign or coding required (unless of course the size difference is so
great it causes forms or reports to obscure the data). 

Now, if your chief reason for choosing ridiculously long field sizes is that
your programs will accept any size input but will blow up if it is beyond a
certain size, we are on two completely different islands here.  I would
humbly suggest that you learn about buffer overflow errors (especially since
these vulnerabilities were discovered in IIS a few years ago, and made
several ASP sites targets of hacker attacks).



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

Good questions.

First of all, if a user needs 255 characters, isn't it our job to provide
that to them?  In my previous 'state abbreviation' example, the 'need' for
setting a two character limit is non-existant, it should be done in the FE,
not in the BE design.  We are all kind of harping on 'set' fields, where a
limit is usually a pretty well educated guess, or even an established fact,
barring no future changes.  Let me give you a different (real life) example.
I wrote an ASP version of a paper form.  The original developer (who I was
actually working for) had some unusual quirks in his system, too many to get
into with email.  However, to handle certain issues, I had to 'ignore' a lot
of errors.  Not really a problem, unless of course the errors are telling
you that data isn't being written for one reason or another.  The system
started as an Access BE, but was later converted to SQL Server, with all of
the 'limitations' that were built into the Access BE.  One such limitation
was a field 'Exact Location where accident occurred'.  This field was set to
a 35 character max.  Because of the forced errorhandling, some people
weren't getting that field's data saved, because they were putting in more
then 35 characters.

Quite frankly, the database I was 'using' should have been completely
redesigned.  It is something the original developer and I have talked about
many times.  So the 35 character limit is just a drop in the bucket.
However, if that limit wasn't set, then an issue would have never arisen
about it.

I'll be honest with you, I don't write a heck of a lot of Access reports.  A
large majority of my FE stuff (including reports) is done in ASP.  However,
when I do use Access, I size the report fields so that they display what
would be normal data.  You can always set the 'Can grow' property.  Besides,
I would personally rather get called to change the size of a report's field,
then get told that they cannot entire data into my database.

I'm pretty sure I haven't said that setting field size limits is wrong.  If
I did, sorry, it's not wrong, I've just been burned many times on the issue.
But then again, I've been burned on all sorts of other things, which just
didn't work when circumstances changed.  I have yet to be burned with
setting the fields to their max size.

Several people have brought up very valid reasons as to why they use
different max field sizes.  Your point about report fields is certainly a
valid argument.  However, I haven't had to work on anything from people on
this list (with the exception of Mike Mattys, and I have absolutely no
complaints there!).  In the cases where I have been burned by previous
developers setting field limits, I would be willing to bet a round of beers,
that 9 out of 10 times, the limits were set due to the myth that setting the
field size smaller decreases the size of your database.

So if you set a limit, and you have a valid reason to do so, more power to
you!  Seriously.  I am just griping on where I have been burned, and trust
me, not a single 'limit' that has burned me was set for the reasons brought
up so far!

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Brett Barabash
Sent: Monday, May 24, 2004 12:22 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


I've been watching this thread for a while now, and need to ask this
question to Drew and JC:

If you always allow 255 characters for your text fields, do you format your
forms and reports to display that size of data?

Drew, you talk about being "burned by these limitations".  It would seem to
me that if your report's fields are not wide enough and truncate the field
contents, you have effectively imposed that limitation.  However, you have
done it in a much more insidious way, by allowing the user to type in a long
string and then not displaying its full contents in the output.  This
seriously breaks one of my cardinal design rules of accepting user input
that the system is not able to process.

Of course, I'm assuming that you don't display all 255 characters.  If my
assumption is wrong and you actually do leave enough room for all 255,
doesn't this result in pretty weird looking tabular reports?  How many 255
character fields can you fit across an 8 1/2 X 11" piece of paper?  1?  2
perhaps?

Have you ever encountered users that misuse the fields?  I have (in almost
every company I've worked for).  Doesn't allowing the entry of 255
characters in any text field (say Address 2) invite the careless user to
treat it as the Memo field they forgot to ask for?

Seems like sloppy programming to me.  And very surprising comments from
developers who preach about following good data modeling practices.


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



----------------------------------------------------------------------------
----------------------------------------
The information in this email may contain confidential information that 
is legally privileged. The information is only for the use of the intended 
recipient(s) named above. If you are not the intended recipient(s), you 
are hereby notified that any disclosure, copying, distribution, or the
taking 
of any action in regard to the content of this email is strictly prohibited.
If 
transmission is incorrect, unclear, or incomplete, please notify the sender 
immediately. The authorized recipient(s) of this information is/are
prohibited 
from disclosing this information to any other party and is/are required to 
destroy the information after its stated need has been fulfilled.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

This footer also confirms that this email message has been scanned
for the presence of computer viruses.Scanning of this message and
addition of this footer is performed by SurfControl E-mail Filter software
in conjunction with virus detection software.

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