What Are The Best Practices For String Data Field Sizes?

Question

Are there any recomended field sizes for commonly used string data storage types? For example things like FirstName, LastName, AddressLine1, AddressLine2, City, State, PostalCode, EmailAddress etc. I am sure anyone who has created a table to store these commonly used data fields has had to make a decision as to what maximum size to use.

What did you base you value on?

Do you have some kind of formula that you use to make the decision(such as all fields sizes are a multiple of 8)?

Does your company have a guideline so all tables that contain these fields are uniform?

Is this just a personal thing?

I know for me if I had a set standard, I wouldn't have to stop and think about it and end up with fields of different sizes. This uniformity would also be great when working on the UI as it would be easier to set the maxlength on input fields and any data validation code.

Answer 1

Keep in mind that the US Postal Service only allows for lines of 40 characters. Thus, 173438 Martin Luther King Memorial Parkway SE, Suite 1124 would be converted by a CASS application into something like:

173438 Martin Luther King Pkwy SE
Suite 1124

For addresses, I would start with the USPS guidelines and then if you plan on supporting international clients, look at those country's postal standards. If the address is never going to be queried, filter, sorted or used on an actual letter, then you might as well make a single column typed as nvarchar(max) (BTW, this never ends up being the case and thus I always end up needed separate columns for city, state, postal code etc).

Of late, I have gotten out of the habit of using individual columns for the lines of a street address. Instead, I use a single nvarchar(max) and split on line break (\r\n or just \n). In this way, I don't have to add a new column when one user wants a five line address excluding the city, state and postal code.

I typically use nvarchar(25) for names. I've yet to run across an individual with one part of their name that was longer than 25 characters. I'm not saying it isn't possible, I've just never seen it in a live environment.

Answer 2

Since August 2009, CASS software like that at http://semaphorecorp.com is required by the USPS to be able to abbreviate addresses to 30 characters. A standardized USPS address is NOT split over two lines.

If you don't use the often-heavily-abbreviated 30-char form, the actual maximum layout allowed by the USPS database would be 10 chars for house number, 2 chars for predirectional, 28 chars for street, 4 chars for suffix, 2 chars for postdirectional, 4 chars for unit, 8 chars for apartment. With blanks between words, that's 64 chars for an address line, although no single address maxes out all those fields. Mail drops would also require private mail box designators like "PMB " on the end of the address.

Search USPS.COM for "Publication 28" for more details.