Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What are the most common best practices on length and data type on common fields like:

  • First Name
  • Last Name
  • Address
  • Email
  • Sex
  • State
  • City
  • Country
  • Phone Number

etc....

share|improve this question
1  
I'd typically recommend separating out address 1 and address 2, since they are almost always going to appear on separate lines and just about every form I've ever seen collects them separately. No reason to glom them together just to tear them apart. –  Aaron Bertrand Jul 11 '11 at 21:23
add comment

5 Answers

up vote 34 down vote accepted

I would tend to be very suspicious of any set of universal best practices because, for most of these fields, the devil is in the details. Just because the information is relatively common doesn't mean that your application uses the data in exactly the same way that other applications use it. That means your data model may need to be slightly different.

  • First & last name: Why are you capturing the name? If you have a requirement to capture a person's full legal name (i.e. you are preparing legal documents or birth certificates), you probably want to allow more space for people to type than you would if you're just asking for a person's name so you have something to call them in your new web app.
  • Address: What are you going to do with the address? What sort of addresses are you storing? If you are storing the address of a property in the United States that you're creating a mortgage on, you likely care very much about getting a fully standardized address in which case the data model will probably want to hew very closely to whatever your address standardization tool returns. If you just want people to be able to type in an address to deliver a product, a couple lines for freeform text is probably sufficient. The length of the lines there may depend on the requirements of the downstream processes that do things like print address labels.
  • State: Assuming you can identify the valid state values, it probably makes sense to create a STATE table and create a foreign key relationship between the STATE and ADDRESS tables. But the ability to identify the valid values implies that you're limiting the set of valid addresses at least to a particular set of countries. That's fine for many sites but then you've got to do a bit of work to support a new country.
  • City: If you are dealing with data where there are potentially city-level regulations in place (i.e. where there are different sorts of tax rates that are applied based on the city), you may want to treat it much like the state and have a CITY table with the valid cities and a foreign key relationship between the CITY and ADDRESS tables. On the other hand, if you're just trying to get a product delivered and you don't much care if you have various versions of the same city in your table, letting the user free-form enter text is sufficient. Of course, if you are storing foreign keys, you'll have a fair amount of work to make sure that you have all the valid values. But there are products where the whole point is that the company has already done that work (i.e. sales tax databases).
  • Phone: What are you doing with phone numbers and why? Some applications will want to take in phone numbers in whatever format the user decides to enter them and preserve that formatting for all subsequent queries. This would be common if you are designing a personal address book where users have their own preferences for how phone numbers are stored and displayed. Other applications would want to ignore the formatting that is entered, extract only the numeric characters, and then format the data on retrieval so that all phone numbers have similar formatting. If you're catering to businesses, you may want a separate field for users to enter an extension. If you are trying to support an outbound calling process, you may want to store the area code and country code in separate columns because you'll want to make sure that you have time zone specific windows for calling people in different area codes (making a call to someone in the Eastern time zone at 10am is going to go over much better than making that same call to someone in the Pacific time zone where it is 7am).
  • Gender: For a great many applications, it's perfectly reasonable to store a gender code ('M' or 'F') in a table. On the other hand, there are cases when you may want additional options (Other, Intersex, Transgendered) or where you need to store something like the gender at birth and the current gender.
share|improve this answer
2  
+1. Your questioning/reasoning method before deciding on the data type is very practical. Nicely put. –  StanleyJohns Jul 11 '11 at 19:15
5  
+1: The best question of all: "What are you doing with it?" –  Adam Paynter Jul 11 '11 at 23:40
1  
+1 for demonstrating the reasoning process. –  mumtaz Jul 12 '11 at 0:40
    
+1 The devil is very much in the details. Address standardization (and for where) completely changes the model needed. Gender isn't always straightforward either. Some systems have more than five or six different choices. –  Thomas Jun 14 '12 at 11:16
add comment

My bum is getting sore from sitting on the fence, so I am going to just throw out some answers and hope to not get down-voted into oblivion. Please offer constructive criticism.

E-mail Address:

min: 6 (a@g.cn) . Or 3 if you want to track local domain email addresses
max: 320 254 (RFC)

The amount of code to validate an email is actually insane, so let's just assume it's valid if it has a "@"

You may want to abstract an email address as a "communication method", so that you can easily list all methods with which to communicate with a user.

Gender

Gender can change over time, so you could track that if it's important to you. Follow http://en.wikipedia.org/wiki/ISO/IEC_5218

NOT_KNOWN(0),
MALE(1),
FEMALE(2),
NOT_APPLICABLE(9);

Addresses: NORAM

I am gonna take the cheap way out and stick to North American addresses.

It is convenient to abstract countries, divisions, cities, and counties mostly due to taxation. Taxes can apply at many levels, so if you can point a tax rate at an abstract geographic area, you are golden.

GeographicArea:

id: int  
type: {country, division, county, city, indian reservation}  
name: varchar(45)  [1]
abbreviation: nullable varchar(4)  
parent_id: nullable int  

Address:

id: int  
postal_area_id: int, references GeographicArea  
county_or_city_id: int, references GeographicArea  
street_address: varchar(255)  
suite: nullable varchar(255)  

Add line2, and line3 if you need to.

See http://en.wikipedia.org/wiki/Address_(geography)

Now, an address is an address. Multiple people can live at an address, and a person can have multiple addresses at the same time, and over time, so you need a many-many table for that.

PartyAddress

party_id: int references Party  
address_id: int references Address  
purpose: {home, work, ...}  

Add a from_date and nullable to_date if tracking over time.

Phone Numbers

A party can have multiple phone numbers, and a phone number can be used by multiple people. A phone number can be used for faxes, telephone calls, modems, etc. and can have extensions. These can all change over time too.

PhoneNumber

id: int  
value: varchar(15) - the max allowed by the ITU  

The min might be 3 (for "911"), or maybe 7 ("310-4NET", which is a special kind of local number that does not allow you to dial the area code)

You could split this into country code, etc if necessary.

You should use the http://en.wikipedia.org/wiki/E.164 standard

PartyPhoneNumber

party_id: int references Party  
phone_number_id references PhoneNumber  
extension: nullable varchar(11) - ITU max  
purpose: {home, work, fax, modem, ...}  

Names

Names are tough. Here's why:

  1. Some people have a legal name with only one word in it http://en.wikipedia.org/wiki/List_of_legally_mononymous_people

  2. Some people have names with many words http://en.wikipedia.org/wiki/Wolfe%2B585,_Senior

  3. Some people have multiple names at the same time (for example, at my university there are many Asian students, but they like to use "preferred" more Westernized names)

  4. Sometimes, you need to track peoples' names over time, such as maiden names and married names.

  5. You want to abstract individuals and organizations for a variety of good reasons

    create table party( id bigserial primary key );

    create table party_name( id bigserial primary key, party_id bigint not null references party(id), type smallint not null references party_name_type(id) --elided, ex "maiden", "legal" );

    create table name_component( id bigserial primary key, party_name_id bigint not null references party_name(id), type smallint not null references name_component_type(id), --elided ex "given" name text not null );

share|improve this answer
add comment

From a slightly different perspective than the previous answers, and since it seems OK to talk about LDAP, RFC 4519 -- "Lightweight Directory Access Protocol (LDAP): Schema for User Applications" may be of interest.

It may be useful if your application needs to be mapped to such a directory. Otherwise, it's probably not adapted to your requirements.

These definitions are more than just about data, they're also about some operators that can be used on the fields. postalAddress, for example is a caseIgnoreListSubstringsMatch. I'm not suggesting that you should adhere to this schema strictly, but looking at the principles could be interesting, in particular how you may have to compare name and addresses in your application may be relevant to the design of your database.

share|improve this answer
add comment

In addition to the great answers above, don't forget to accept unicode characters. Just because you are in the US doesn't mean that you don't want to accept foreign characters into your columns.

That said, I usually recommend 50 characters for names. 320 should be more than enough for an email address (you can check the ANSI standard to be sure). For address error on the side of caution with 255 characters. While you'll probably never need an address that big, you might if you include C/O lines and stuff like that. City should be pretty big, there are some pretty long city names out there. For state go with a child table, same with country. For Zip code don't forget about international postal codes which are longer than US zip codes. Just because you don't support international you still might be. There are lots of US citizens who live in different counties including military folks.

Don't forget that state should be optional as many countries don't have states.

share|improve this answer
    
I've always used 320 for e-mail address. When I worked at an e-mail service provider, the standard said 64 for localpart + '@' + 255 for domain. The standard may have evolved since then, but we did have the occasional customer that had > 255 (probably fake addresses meant to check compliance with their standards). –  Aaron Bertrand Jul 11 '11 at 21:19
    
That number of 320 does sound very familiar. I'll update my answer with that number. –  mrdenny Jul 11 '11 at 21:21
    
On my last project, I found a document on international postal standards that indicated 39 as the maximum line length. France has a separate code for large volume recipients that goes after the city. I would allow 3 or 4 free format fields of this size plus country. –  BillThor Jul 12 '11 at 18:35
add comment

You may as well guess based on sample data and expected audience. It depends on your location.

Some notes:

Addresses:

Names:

Phone number: International code, length, mobile vs house, allow mobile as only number

share|improve this answer
1  
+1 for "I don't have a state". –  Dan Jul 25 '11 at 19:33
    
+1B for international thoughts #loveYourData –  billinkc Aug 13 '11 at 19:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.