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