How-to: Database field sizes

Some popular best practices on length and data type for common database fields.

 Title          Max 35 chars (Mr,Mrs,Miss,Ms,Dr,Rev,Sir,Lady,Lord,Captain,Major,Professor,Dame,Colonel.)

 Gender         Unknown(0), Male(1), Female(2), Indeterminate:Male+Female(3),
                Male from Female(4), Female from Male(5), Not Applicable(9) 

 First Name     Max 35 chars (no spaces)
 Last Name      Max 35 chars
 Display name   Max 70 chars
 NameSuffix     Max 35 chars (OBE, MBE, BSc etc)

 Address
    Flat Number/Name  Max 35 chars
    Building Number   Max 35 chars
    Street            Max 35 chars
    Town              Max 35 chars
    State/County      Max 35 chars (Optional)
    Postcode/Zipcode  Min  6 chars Max 9 chars (some countries use non numeric codes)
    Country           Max 70 chars (ISO 3166)

 Email          Min 7 Chars, Max 255 chars  A full validated email address  

 Phone Number   Max 15 chars (no spaces) ITU E.164

Setting sensible maximum sizes improves data quality by minimising copy/paste errors, if First_Name accepts 2000 characters then sooner or later somebody will paste the entire contents of an HTML email into that field.

Some of the recommendations above are somewhat larger than real data samples might suggest is necessary but they provide space to accomodate possible future changes e.g. the formation of a new country with a very long name.

Splitting address into several fields will simplify the design of automated letters & mail merge.

Use unicode, other text encoding schemes will not prevent users from entering carets or umlauts, they just make displaying the text difficult.

The 'Display name' can default to a concatenation of Title+ First + Last name, display names are often used for unofficial 'known as' names , e.g. William Helkathhazzurim is known to everyone as Bill Helka.

Som applications, such as credit card processing require a distinct separate First and Last name. In less formal applications the display name may be the ony name displayed to the end user.

“I'd call it a new version of voodoo economics, but I'm afraid that would give witch doctors a bad name” - Geraldine A. Ferraro

Related commands

UK Data Type Standard(pdf) - e-Government Interoperability Framework (e-GIF)
BS7666 - The British standard for the representation of address information
IETF RFC 3696 - Checking email addresses
IETF RFC 2822 - Data standard for email addresses
ISO/IEC 5218 - Gender codes (additions are not limited to those in the standard)


 
Copyright © 1999-2024 SS64.com
Some rights reserved