4

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

  • 1
    What does "best" mean? If I suggest first name as nvarchar(50) but you have users with longer first names, does that make it worse? – Aaron Bertrand Dec 16 '14 at 17:42
  • 1
    Whatever is suggested, make it larger.... – kevinsky Dec 16 '14 at 17:45
  • @AaronBertrand i mean by best as standard format ? – john Gu Dec 16 '14 at 17:50
  • 1
    What is the standard format of a telephone number or a first name? Do you only accept phone numbers from one country code? – Aaron Bertrand Dec 16 '14 at 17:51
11
  • 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.

  • thanks for the reply, i am using asp.net mvc which automatically takes care for SQL injection attacks and i will also add server& client side validations for the data types. but regarding the telephone number i need to support number from multiple country codes,, so which data type best fit? – john Gu Dec 17 '14 at 2:06
  • @johnG A varchar that fits the longest number you need to support. – Aaron Bertrand Dec 17 '14 at 2:11
  • what do u mean exactly ? can you adivce please ? – john Gu Dec 17 '14 at 13:38
  • What I mean exactly is if you have a phone number like +44 0123 4567 then you need at least a varchar(13). – Aaron Bertrand Dec 17 '14 at 14:00
  • i checked the attributes on active directory seems that first-name can not exceed 29 characters and same apply to last name. for the telephone it can not exceed 64 characters,, seems it is a good source to such info .. waht do u think ? – john Gu Dec 18 '14 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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