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

Charlotte Foust cfoust at infostatsystems.com
Tue May 25 13:40:37 CDT 2004


No, it's up to your interface and business logic layers to control them.  The client just applies penalties.

Charlotte Foust

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


I still wouldn't have a problem with that, because it's the clients data.  I can setup business rules as they request, but I can't control THEIR users. That's up to them to do.

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 9:18 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


<< I have yet to actually have any issues with setting my fields to that.

You probably wont until you get a piece of data, "ridiculously long" <VBG>, that you probably don't want in the database anyway. (ie. "John Jacob Smith, Jr." in the FIRSTNAME field).

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 5:16 PM
To:	accessd at databaseadvisors.com
Subject:	RE: [AccessD] On DB Bloat, Bad DB Design, and various

Wasn't trying to be argumentative on this one.  I have just been bitten by 'limited' text field sizes quite a bit, and never the other way around.

I can appreciate your point of view, and style.  I think I just bristled at the 'ridiculously long' comment.  255 characters is not that long, and I have yet to actually have any issues with setting my fields to that.

Cool?

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 3:35 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


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

I thought that we had already established the whole database size thing. Allow me to clarify:

I do not limit field sizes to save space in the database.
I am aware that smaller field length limits do not mean a smaller database. Field size limits are part of data validation.  Nothing more, nothing less.

Do I set my fields to 50 by default?  Of course not, that makes about as much sense as setting them to 255.  I set them to the appropriate length for the anticipated data.

Let's take a first name for example:
Is it reasonable to expect a 255 character first name?  No.  That would be, uh, RIDICULOUS (thus my comment). 50 would be more than enough.  How about 35?  Likely, yes.

I can already hear the voices of outrage now. "What if you someone has a 36 character first name?  Your system would blow up.  Wouldn't it just be easier to allow 50 and avoid this possibility?"

First of all, it wouldn't blow up (like other apps I've heard mentioned today), it would gracefully limit the size. More importantly, my reports have been designed to meet this design tolerance.  If I allowed 50 characters when 35 is more than ample, I would need to rework my reports to allow the larger fields.

"Why don't you just truncate the field on the report?" is the next question, to which I say "Because the user thinks that what they just entered is OK, and it clearly isn't".  If they can see that the system doesn't allow a 36 character name, they are aware of the limitation, as opposed to the surprises down the road when their output gets clipped.


-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com]
Sent: Monday, May 24, 2004 2: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