Jürgen Welz
jwelz at hotmail.com
Mon Dec 5 22:17:34 CST 2005
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 >From: "John Colby" <jwcolby at colbyconsulting.com> > >I just ran into a field the other day. PolicyID, guaranteed to NEVER be >more than 10 characters, so the field was set to 10 characters (by the >previous programmer). > >Guess what? We finally (3 years later) got a policed that was waaaay more >than 10 characters. Of course the entire policy record could not go in, >which prevented the claim from being processed. All users must get out of >the database so that I can open up the field (to 255 characters of course). > >I don't care WHAT the business rule is, text data can and will change. SSN >is a good example. It is guaranteed to be XXX-XX-XXXX except that they are >running out of SSNs (50 years later) and guess what is going to change in >the next few years... > >Length types of rules are not the thing (IMHO) that should be enforced at >the DB level. > >John W. Colby >www.ColbyConsulting.com