What are the most common best practices on length and data type on common fields like:
- First Name
- Last Name
- Phone Number
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.
You may as well guess based on sample data and expected audience. It depends on your location.
Phone number: International code, length, mobile vs house, allow mobile as only number
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.
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.
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.
min: 6 (firstname.lastname@example.org)
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 can change over time, so you could track that if it's important to you. Female is probably the default gender of humans, but is_male is shorter :) This is for "current" gender:
is_male: nullable boolean
You could also track whether the person was born male or not:
born_male: nullable boolean
I am gonna take the cheap way out and stick to North American addresses.
Some cities contain zip codes, and some cities are contained within zip codes, so there is not a child/parent relationship. Same for counties. Therefore, zips, counties, and cities are all children of division, which is a state, province, or territory. A division is a child of country.
It is convenient to abstract countries, divisions, cities, counties, and zips 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.
Add line2, and line3 if you need to.
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.
party_id: int references Party
Add a from_date and nullable to_date if tracking over time.
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. These can all change over time too.
You could split this into country code, etc if necessary.
party_id: int references Party
There was a really good schema on stackoverflow which I now cannot find. A summary:
A party can have many names, and those names are used in different contexts. Names can change over time as well (think marriage, divorce, Prince). A name is a combination of name components.
Here's the easy way, with current names only: