Tuesday, August 07, 2007

Choosing appropriate field lengths

For every project that I work on I seem to have the same argument with myself about field lengths (mainly when dealing with SQL) - just how long should they be? What is appropriate for first name, last name, and email address columns?

I used to start big, 100 characters for first/last name and 255 characters for email addressses. Recently I was revisiting my thinking of lengths so I decided to run some stats on the one of the larger DB's that I maintain. Here's what I found.

First Name: Max 27, Avg 6
Last Name: Max 27, Avg 6
Email: Max 76, Avg 22

The first thing that hit me was, geeze, a 76 character email address - that's a lot of typing! The second thing was that my column length allocations were on the really high (and unnecessary) end of the spectrum.

I always like to leave a bit of room because users always seem to do the unthinkable, so moving foward, my allocations look like:

First & Last name: 35
Email: 100

Since I mainly use variable length columns (varchar/nvarchar), storage is never really an issue, however when you start allocating 255 characters to each column it is easy to hit the table maximum (w/o BLOB's). Knowing that I can tone down my allocations gives me some re-assurance that I am less likely to reach the maximum when adding new columns down the road.

No comments: