7

Possible Duplicate:
List of standard lengths for database fields

Simple as that, what should be the typical length of allowed "Full Name" of a user in database?

When I create users table, I usually set it as varchar 31 or 32 (according to performance). What do you guys use and what's standard/typical convention.

Sidenote: I never face problem in email length (as I set it 254) and password (hash, 32 length).

Community
  • 1
  • 1
WhatIsOpenID
  • 1,071
  • 4
  • 12
  • 20

4 Answers4

7

The maximum your average varchar field allows (254?).

You are not winning anything by making it arbitrarily shorter. The fine-grained size controls on numbers and chars are more or less a relic from the past, when every byte mattered. It can matter today - if you are dealing with tens or hundreds of millions of rows, or thousands of queries per sec. For your average database (i.e. 99% of them) performance comes from proper indexing and querying, NOT making your rows a couple of bytes smaller.

Only restrict the length of a field when there is some formal specification that defines a maximum length, like 13 digits for an EAN code or 12 characters for an ISIN.

Martin
  • 1,622
  • 4
  • 16
  • 27
  • 3
    It's not about saving database space. It's about cluttering it lesser. Like shamittomar said, "Imagine people putting 128 char long names in Facebook and comments showing it. Almost whole line will get covered up by comments only." – WhatIsOpenID Nov 28 '10 at 16:58
  • 2
    Depends on where you need it. If it is a real name, I'd recommend not restricting it in the database, but truncating the name for display where appropriate (eg display the first 30 chars and use the ``-Tag). If its a username, just pick a number, 32 should be fine. – Martin Nov 28 '10 at 17:08
4

Full name is always a computed column composed of first, middle, last, prefix, suffix, degree, family name, etc in my designs. The list of individual columns are determined by the targeted local of the app. The display length of 'Full Name' is normall contained within the app design not the database. There is not any space savings in SQL Server between varchar(32) and varchar(256). Varchar(256) is my choice.

I never want to be in the meeting when someone says "Your db design will not hold all our data".

You are always assigning an ID to the user so you can join and do look-ups using the ID instead of the FullName, correct?

RC_Cleland
  • 2,274
  • 14
  • 16
2

I would recommend at least 128.

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
1

Well you can just put it at 255 if you want. varchars is a Variable length storage type. This means theres 1 byte which stores the actual length of the string, varchars dont use up more bites then needed so storage wise it really does not matter. This is described on the mysql page

Description can be found here http://dev.mysql.com/doc/refman/5.0/en/char.html It is illustrated halfway the page check the table.

VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

Conclusion: Storage wise you could always go for 255 because it wont use up additional space and you wont get intro trouble with string getting cut off.

Greetz

Michael
  • 1,201
  • 1
  • 8
  • 15