Default size for database fields

Question

What the size that you use for common database fields, like firstName, lastName, Email, password, etc? I see these common fields in a lot of databases of blogs, forums, e-commerces, etc. But I don't know if there is some reference or default for the size for that common fields. So, I want to know what the method/reference/basis that you to use for selecting size for common fields.

Answer 1

Partly, it depends on your DBMS. Some, like MySQL 5, care about the length of a VARCHAR(n) column as opposed to an unlimited-length TEXT column; others, like PostgreSQL, consider TEXT and VARCHAR(n) as internally identical, with the exception that a length is checked on VARCHAR(n) columns. Writing something like VARCHAR(65536) in PostgreSQL is silly; if you want an unlimited-length column, choose TEXT and be done with it.

Of course, sometimes trying to store too long of a value will break your layout, or allow someone to abuse the system by choosing a long name with no spaces (for example). Usually what I do for username fields like that is just choose an upper length such that anyone who wants a longer username is trying to cause trouble; 64 characters is a nice round value and seems to work well. For real names and addresses (which aren't frequently displayed to users like a username is), you'll want to go with something longer. You want a value large enough that it can accept any valid input, but not so large that someone could stuff a gigabyte-long string in the field just to attack your system. 1024 characters is pretty reasonable: 1k is an small enough amount of text to easily work with, a round number, and larger than any sane address line or name.

Email addresses can, per the associated RFC whose number I am too lazy to look up right now, be no longer than 320 characters. So there's your email field length. Turns out that SMTP limits the length of fields to 256 characters; since email addresses must be bracketed, the longest valid email address is actually 254 characters. (This page goes into more detail.) So there's your email field length.

Passwords should NEVER be stored in plaintext, so your password field should be a byte array or BLOB type exactly long enough to store the output of the hash function you are using (or largest element of the cryptographic group in use, for more advanced schemes like SRP-6a).

Answer 2

What I tend to do is think of how long a field's value could possibly be, then double that to be safe.

E.g. Name: varchar(70) Email: varchar(200)

Answer 3

i like 16, 32, 64, 128, or 256

Answer 4

Double your estimates. And then redouble them to accomodate unicode.

Databases which have varchars (almost all do) incur a very small penalty for fields which are designed to hold longer values but do not. You can use that to your advantage.

Answer 5

Try to think this out in reverse: I've used existing data to get reasonable field lengths. Assuming you have access to a good-sized database filled with real data, a quick query like:

SELECT MAX(LEN(lastname)) FROM dbo.MyDatabase

will give you everything you need.

UPDATE: Don't use the number you get exactly. Obviously, pad it up a little depending on your uncertainty, unless you have a very large sample set.