What VARCHAR sizes should be used for first and last names, phone numbers (preferably international), email adresses, urls, dictionary words and file names?

Edit: Zapped the introductory phrase to form a concrete question.

  • 2
    Can you make your question more specific? Open-ended list-generating questions aren't constructive. – Caleb May 14 '12 at 7:38
  • @caleb what further information can I add? To reformulate, I would simply like to know what field sizes I could/should use according to the nature of data that needs to be stored. – James P. May 14 '12 at 8:12
  • The nature of typical types of data might I add. – James P. May 14 '12 at 8:35
  • From the faq: "You should only ask practical, answerable questions based on actual problems that you face. Chatty, open-ended questions diminish the usefulness of our site..." So please, instead of "typical types of data," ask about the specific types of data that you're dealing with in the problem that you currently face. – Caleb May 14 '12 at 12:23
  • 1
    It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. – user7519 May 14 '12 at 21:45

The rules of thumb I use are:

  1. Try not to assume any max size.
  2. If you must assume a max size, look for any standard regarding the field. Email for example has a max size of 256. Phone numbers have standards that differ depending on scope.
  3. If you must assume max size, and you have no standard to go by, pick something ridiculously large and then assume someone will come along with data that exceeds it.
  • 4
    +1. But when picking ridiculously large sizes, one should make sure the intended database system does not reserve the full amount of memory for each data row if the memory is not needed. – Doc Brown May 14 '12 at 15:29
  • Ok. Assume the database here is MySQL. – James P. May 15 '12 at 7:54

The answer depends on the database you are using. For Postgres, you may as well use TEXT for everything; no other type will be more efficient. For Oracle you can use VARCHAR2(4000) for all fields where 4000 characters is sufficient; again no other character type will be more efficient.

  • I understand. I should have added to the question that this was at the modeling phase but this is useful information to know. – James P. May 15 '12 at 8:13
  • @James: this is an implementation decision, not a modeling decision. When modeling you can note the type (text) and the maximum expected length, but should not decide on an implementation. – kevin cline May 15 '12 at 17:15
  • Making people happy*. – James P. May 19 '12 at 20:34

there are really two schools of thought for how to determine field size.

  1. make everything as big as possible.

This makes it unlikely that it will ever be required to expand a field which can be very expensive to do, also with fields like varchar there is very little lost in terms of wasted space/performance.

  1. Create fields with smallest possible limit.

This can help increase data integrity and also be very useful in special cases. For example if a field is commonly used in a report where space is an issue limiting it to X characters saves having to decide how to trim or truncate data. Limiting sizes can also be useful if you can guarantee a field will always have a specific length, this can help ensure your data has a minimum quality, for example storing state postal codes as a CHAR(2) ensures that longer entries won't happen, or storing numeric values as a numeric type ensures that what is stored is a number. It can be extremely hard to find numbers for field length for most fields though and the cost of having to change after implementing can be very expensive.

The best rule of thumb is do what makes your DBA happy.


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.