Q:

Standard data types for email address, telephone number , first name, rixh text editor, etc

I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:-

Email address
Telephone number with extension, such as +44 12345 ext 123
First Name
Rich Text editor. we have a field named "Position Role" or "Main duties", which stores a full description of each employee's roles.

Can anyone adivce on this please?
Thanks

A:

E-mail address: I go with NVARCHAR(320) - 64 characters for local part + @ + 255 for domain name. You may also consider normalizing the domain away, since it's pretty wasteful to store hotmail.com 500,000 times when a much smaller INT or even SMALLINT is probably capable (I don't know how many domains you plan to support). I used to be against NVARCHAR but the standard has expanded to support Unicode characters, so even though most mail servers in the world will reject them, better safe than sorry. Next will be to enforce validation - you can read all you want to know about valid formats, and then some, on Wikipedia.
Telephone Number: Probably some varchar in the 25-30 range (here you don't need Unicode support). Unless you know for sure you will only support phone numbers from certain geographical areas, then your choice is much simpler. What is "best" is hard for us to gauge - do you want perfect storage for the formats you support now, or more flexible storage in case you support more later or in case people are sloppy at entering form data and you still want to capture whatever they entered.
First name: Completely subjective. Probably nvarchar(50) but I agree with @kevinsky - ask the business people what they think the longest first name is (or, if you have source data in another system, verify), then double it. Because I promise as soon as someone puts some artificial limit on the length, someone will come along and break the rule.
Role: Again, this is pretty subjective. Is this supposed to be a paragraph of text, or an autobiography? You will need to decide if you want to allow some finite number of characters (say, nvarchar(1000)) or much larger than standard formats (e.g. nvarchar(max), which supports up to 2GB of data per row). You should also take total row size into consideration - it may be easy to pick nvarchar(4000) but you will quickly exceed the row size and force some of that data to be stored inefficiently if people really use it. If a role is tied to a title and not a specific person, this should probably be stored elsewhere anyway.

Make sure your form fields are set to these max lengths, make sure any input is validated and sterilized, and make sure you use parameterized inputs to avoid SQL injection attacks.

