Re: Optimization of this SQL sentence

alex(at)purefiction(dot)net (Alexander Staubo) writes:

> On Oct 17, 2006, at 17:29 , Mario Weilguni wrote:
>
>> Am Dienstag, 17. Oktober 2006 11:52 schrieb Alexander Staubo:
>>> Lastly, note that in PostgreSQL these length declarations are not
>>> necessary:
>>>
>>> contacto varchar(255),
>>> fuente varchar(512),
>>> prefijopais varchar(10)
>>
>> Enforcing length constraints with varchar(xyz) is good database
>> design, not a
>> bad one. Using text everywhere might be tempting because it works,
>> but it's
>> not a good idea.
>
> Enforcing length constraints is generally a bad idea because it
> assumes you know the data domain as expressed in a quantity of
> characters. Off the top of your head, do you know the maximum length
> of a zip code? A street address? The name of a city?

In the case of a zip code? Sure. US zip codes are integer values
either 5 or 9 characters long.

In the case of some of our internal applications, we need to conform
to some IETF and ITU standards which actually do enforce some maximum
lengths on these sorts of things.

> In almost all cases the limit you invent is arbitrary, and the
> probability of being incompatible with any given input is inversely
> proportional to that arbitrary limit.

I'd be quite inclined to limit things like addresses to somewhat
smaller sizes than you might expect. If addresses are to be used to
generate labels for envelopes, for instance, it's reasonably important
to limit sizes to those that might fit on a label or an envelope.

> Encoding specific length constraints in the database makes sense
> when they relate explicitly to business logic, but I can think of
> only a few cases where it would make sense: restricting the length
> of passwords, user names, and so on. In a few cases you do know with
> 100% certainty the limit of your field, such as with standardized
> abbreviations: ISO 3166 country codes, for example. And sometimes
> you want to cap data due to storage or transmission costs.

There's another reason: Open things up wide, and some people will fill
the space with rubbish.
--
"cbbrowne","@","acm.org"
http://linuxfinances.info/info/internet.html
"The Amiga is proof that if you build a better mousetrap, the rats
will gang up on you." -- Bill Roberts bill(dot)roberts(at)ensco(dot)com