List of standard lengths for database fields

Question

I'm designing a database table and once again asking myself the same stupid question: How long should the firstname field be?

Does anyone have a list of reasonable lengths for the most common fields, such as first name, last name, and email address?

Answer 1

Just looking though my email archives, there are a number of pretty long "first" names (of course what is meant by first is variable by culture). One example is Krishnamurthy - which is 13 letters long. A good guess might be 20 to 25 letters based on this. Email should be much longer since you might have [email protected]. Also, gmail and some other mail programs allow you to use [email protected] where "sometag" is anything you want to put there so that you can use it to sort incoming emails. I frequently run into web forms that don't allow me to put in my full email address without considering any tags. So, if you need a fixed email field maybe something like [email protected] in characters for a total of 90 characters (if I did my math right!).

Answer 2

it is varchar right? So it then doesn't matter if you use 50 or 25, better be safe and use 50, that said I believe the longest I have seen is about 19 or so. Last names are longer

Answer 3

I just queried my database with millions of customers in the USA.

• The maximum first name length was 46. I go with 50. (Of course, only 500 of those were over 25, and they were all cases where data imports resulted in extra junk winding up in that field.)

• Last name was similar to first name.

• Email addresses maxed out at 62 characters. Most of the longer ones were actually lists of email addresses separated by semicolons.

• Street address maxes out at 95 characters. The long ones were all valid.

• Max city length was 35.

This should be a decent statistical spread for people in the US. If you have localization to consider, the numbers could vary significantly.

Answer 4

I usually go with:

Firstname: 30 chars
Lastname: 30 chars
Email: 50 chars
Address: 200 chars

If I am concerned about long fields for the names, I might sometimes go with 50 for the name fields too, since storage space is rarely an issue these days.

Answer 5

Just make sure you allow non-alpha characters in the names!

points at hyphen in his last name

Answer 6

I would say to err on the high side. Since you'll probably be using varchar, any extra space you allow won't actually use up any extra space unless somebody needs it. I would say for names (first or last), go at least 50 chars, and for email address, make it at least 128. There are some really long email addresses out there.

Another thing I like to do is go to Lipsum.com and ask it to generate some text. That way you can get a good idea of just what 100 bytes looks like.

Answer 7

I pretty much always use a power of 2 unless there is a good reason not to, such as a customer facing interface where some other number has special meaning to the customer.

If you stick to powers of 2 it keeps you within a limited set of common sizes, which itself is a good thing, and it makes it easier to guess the size of unknown objects you may encounter. I see a fair number of other people doing this, and there is something aesthetically pleasing about it. It generally gives me a good feeling when I see this, it means the designer was thinking like an engineer or mathemetician. Though I'd probably be concerned if only prime numbers were used. :)

Answer 8

If you need to consider localisation (for those of us outside the US!) and it's possible in your environment, I'd suggest:

Define data types for each component of the name - NOTE: some cultures have more than two names! Then have a type for the full name,

Then localisation becomes simple (as far as names are concerned).

The same applies to addresses, BTW - different formats!

Answer 9

UK Government Data Standards Catalogue details the UK standards for this kind of thing. It suggests 35 characters for each of Given Name and Family Name, or 70 characters for a single field to hold the Full Name, and 255 characters for an email address. Amongst other things..