CODEWITHSUNDEEP
sql-serverdatabasedatabase-designdata-structures
DaveB
DaveB

Reputation: 9530

What Are The Best Practices For String Data Field Sizes?

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.

Upvotes: 5

Views: 3043

Answers (1)

Thomas
Thomas

Reputation: 64635

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.

Upvotes: 4

Related Questions