List of standard lengths for database fields

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.


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..


Some probably correct column lengths

                            Min Max

Hostname                    1   255
Domain Name                 4   253
Email Address               7   254
Email Address [1]           3   254
Telephone Number            10  15      
Telephone Number [2]        3   26  
HTTP(S) URL w domain name   11  2083        
URL [3]                     6   2083    
Postal Code [4]             2   11
IP Address (incl ipv6)      7   45
Longitude                   numeric 9,6
Latitude                    numeric 8,6
Money[5]                    numeric 19,4

[1] Allow local domains or TLD-only domains
[2] Allow short numbers like 911 and extensions like 16045551212x12345
[3] Allow local domains, tv:// scheme
[4] http://en.wikipedia.org/wiki/List_of_postal_codes. Use max 12 if storing dash or space
[5] http://stackoverflow.com/questions/224462/storing-money-in-a-decimal-column-what-precision-and-scale

A long rant on personal names

A personal name is either a Polynym (a name with multiple sortable components), a Mononym (a name with only one component), or a Pictonym (a name represented by a picture - this exists due to people like Prince).

A person can have multiple names, playing roles, such as LEGAL, MARITAL, MAIDEN, PREFERRED, SOBRIQUET, PSEUDONYM, etc. You might have business rules, such as "a person can only have one legal name at a time, but multiple pseudonyms at a time".

Some examples:

names: [
  {
    type:"POLYNYM",
    role:"LEGAL",
    given:"George",
    middle:"Herman",
    moniker:"Babe",
    surname:"Ruth",
    generation:"JUNIOR"
  },
  {
    type:"MONONYM",
    role:"SOBRIQUET",
    mononym:"The Bambino" /* mononyms can be more than one word, but only one component */
  },
  {
    type:"MONONYM",
    role:"SOBRIQUET",
    mononym:"The Sultan of Swat"
  }
]

or

names: [
  {
    type:"POLYNYM",
    role:"PREFERRED",
    given:"Malcolm",
    surname:"X"
  },
  {
    type:"POLYNYM",
    role:"BIRTH",
    given:"Malcolm",
    surname:"Little"
  },
  {
    type:"POLYNYM",
    role:"LEGAL",
    given:"Malik",
    surname:"El-Shabazz"
  }
]

or

names:[
  {
    type:"POLYNYM",
    role:"LEGAL",
    given:"Prince",
    middle:"Rogers",
    surname:"Nelson"
  },
  {
    type:"MONONYM",
    role:"SOBRIQUET",
    mononym:"Prince"
  },
  {
    type:"PICTONYM",
    role:"LEGAL",
    url:"http://upload.wikimedia.org/wikipedia/en/thumb/a/af/Prince_logo.svg/130px-Prince_logo.svg.png"
  }
]

or

names:[
  {
    type:"POLYNYM",
    role:"LEGAL",
    given:"Juan Pablo",
    surname:"Fernández de Calderón",
    secondarySurname:"García-Iglesias" /* hispanic people often have two surnames. it can be impolite to use the wrong one. Portuguese and Spaniards differ as to which surname is important */
  }
]

Given names, middle names, surnames can be multiple words such as "Billy Bob" Thornton, or Ralph "Vaughn Williams".

Tags:

Database

Standards

Recent Posts