Length of character columns
What are some good rules for specifying column length? People will arbitrarily use "VARCHAR (50)" for name fields and so on, but most are just guesswork. Does anyone have any resources they are using? What are the rules of thumb that you use to indicate the length of a character? For example, for a URL - they can be long, especially if a maximum length is given, but using VARCHAR (MAX) seems to be from the top, although this makes the VARCHAR only take up the space needed for the actual string data.
Request to report / share -
source to share
RFCs for things like URLs and email addresses can be helpful. URLs have a 2083 character limit, while email addresses have a maximum length of 320 characters (64 for a name, 1 for @, and 255 for a domain).
In general, I am wrong on the "too long" side for anything storing user data. For internal use, I try to be more restrictive. I usually use 50 for keys, 255 for description, etc. You won't see much of a performance impact anyway if you don't do searches or whatever, so a lot of these options come down to your personal preference.
In short:
- There is a specification for this data (URL, email, etc.): be precise
- User data: keep using things like names for a long time, set a reasonable limit for things like comment text.
- Indoor use: anything your boat floats
source to share
Ok, generally I consider the expected tenth longest data record I can imagine for a field, and then usually add 10-20% to that as a starting point, unless I know the data has a certain limit (zip- codes are of a specific length, for example) and I use that. If the length is in the hard limit (it should be that length and none other, I use the Char data type, otherwise I use varchar or nvarchar for string data.
source to share
In mysql, at least the overhead from a VARCHAR field is either one byte for a maximum width of 255 bytes, or two bytes for any larger maximum width. Thus, one good rule of thumb is to use VARCHAR (255) when you don't expect your data to exceed 255 bytes, or VARCHAR (65535) elsewhere.
Of course, if you expect this to be a real hard limit on the data, you can set the constraint in the column definition to act as a second layer of data validation (but if it gets large enough to cause a database error, it should be considered an error for more high levels).
source to share
For the url, I will consult the RFC for urls. The first and last name are usually 100 characters long - it doesn't really matter since you said the (N) VARCHAR only takes up the space it needs.
Things I usually do:
- I used to go with NVARCHAR instead of VARCHAR for names, as many names needed special characters.
- I'm pretty sure that no matter what length I choose in the DB, I also apply that in the UI.
source to share
Running with caution, not yet conservative, can allow you to expand later, starting at enormous lengths, can introduce unknowns for performance and use — and can potentially complicate them later.
Do you have many and many, many and many columns that you don't know about? Will they change the monstrous task later? If not, then play it conservatively, and if you hit the limit then you can expand - just keep in mind that you don't want these limits to propagate throughout your code; centralize them as much as possible and provide them where needed.
Try to choose a length that is longer than you expect, but don't completely rule it out. If it's a field that you think 25 is reasonable, then make it 50; if 150 seems right then make it 200.
We usually use nvarchar (100) for names, 255 for URLs or other fields that can be long (these are usually just domain names, not full URLs for specific pages / site resources). Our mainframe imposes similar restrictions and we have a few field extensions every year, but we expected that it wouldn't matter much to change them.
source to share
What are you trying to optimize? As stated, a regular VARCHAR doesn't make much of a difference in cost, so it shouldn't be worth the effort. Another reason you might be interested is some kind of validation rule, in which case you need to figure out what determines whether the field value is valid or not. Then apply this.
source to share