4

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.

Improve this question
13
  • 2
    Can you make your question more specific? Open-ended list-generating questions aren't constructive. Commented May 14, 2012 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. Commented May 14, 2012 at 8:12
  • The nature of typical types of data might I add. Commented May 14, 2012 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. Commented May 14, 2012 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. Commented May 14, 2012 at 21:45

3 Answers 3

Reset to default
10

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.
Improve this answer
2
  • 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. Commented May 14, 2012 at 15:29
  • Ok. Assume the database here is MySQL. Commented May 15, 2012 at 7:54
2

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.

Improve this answer
3
  • I understand. I should have added to the question that this was at the modeling phase but this is useful information to know. Commented May 15, 2012 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. Commented May 15, 2012 at 17:15
  • Making people happy*. Commented May 19, 2012 at 20:34
1

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.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.