2

When I add user info to MySQL through a PHP registration form, there are with limits on the data fields (e.g. name is 20 max chars, email 18 chars, additional info 200, pass 12 chars, etc.)

Should I create exact same fields in the MySQL table, or I should define longer fields?

Is there any benefits of doing so rather than just creating all string fields e.g. 500 characters long?

When storing age as an integer, should I use a small int (i.e. with max 256) or not?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
MrVon66
  • 21
  • 1
  • Limiting ages to a small int makes your code immortality-incompliant. – Marc B Feb 27 '11 at 19:45
  • Just a quick note: You should not have the password as plaintext in your database. Store it md5-encrypted. – DKSan Feb 27 '11 at 19:46
  • 2
    Quick note @DKSan: Don't store passwords MD5 hashed. It's not encryption, and it's not strong enough. Use a better hash function, but more importantly use a good salt. And even more importantly, stretch it well. Don't think you're any safer just by having `md5($password)` because it's only marginal... – ircmaxell Feb 27 '11 at 19:51
  • Why not md5 hashed? What is unsafe if i do md5(md5($password) + $salt) or some other stuff containing pass and salt inc. md5() – DKSan Feb 27 '11 at 19:55
  • @DKSan: Read [this answer](http://stackoverflow.com/questions/4948322/fundamental-difference-between-hashing-and-encryption-algorithms/4948393#4948393) for some more insight... – ircmaxell Feb 27 '11 at 20:08

4 Answers4

1

In general, it doesn't really matter. The important part is how you validate the information on the server side.

  1. Make sure the entered data does not exceed the size of the column. If you don't, you can run into issues where mysql will auto-truncate the data.

  2. Don't limit the password size. If someone wants to enter a 200 character password, let them. You should be storing it in a storing hash and not in plain text, so the exact length shouldn't make a difference.

  3. Always store your data types properly. If you expect an integer age, store it in an integer column. There's no real reason to store it in a string column type.

  4. As far as the rest of your limits, it's really application dependent more than anything. If you expect 200 character info limit, then store it in a VARCHAR(200). But if you're just assuming, store it in a TEXT type so that the user can enter as much as they'd like. But that's more application and use-case dependent than anything else...

Community
  • 1
  • 1
ircmaxell
  • 163,128
  • 34
  • 264
  • 314
1

Suggest you be liberal with your database column lengths (for your varchar), but strict on your application in enforcing size/lengths.

The business logic may change over time. Your application tier will be the keeper and enforcer of those rules.

Your database shouldn't have to adjust often to the changing business rules regarding length. Defining a column of type varchar(100) doesn't cost you anything today. The length is variable up to 100, so your performance and storage won't suffer at all.

Application and database changes/maintenance are expensive; database storage is cheap.

Some other detailed suggestions, if you will:

  • don't store age. Derive it from a date (birthdate) by using math (Today-Birthdate).
  • passwords shouldn't be stored or have a max length!
  • all your string fields -- define them as varchar(256) or 1024 and be done with them. Let your application enforce the business rules of the day.
p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • I think you meant *passwords **shouldn't** be stored or have a max length*. Right now, it doesn't really make sense... – ircmaxell Feb 27 '11 at 20:32
0

If you're using the MyISAM table type it will be a bit more efficient for queries if you keep the record length static. So if you can use char fields of a fixed size instead of varchar it's better (as long as all the fields in a record are static). However, the whole number of characters you specify will be blocked out in memory so you need to decide if memory usage is more important.

Ewan Heming
  • 4,628
  • 2
  • 21
  • 20
0

Unless your application is huge and your DB is going to be massive, this should matter very little in terms of performance. I would say that you should give yourself a bit of extra room in the MySQL fields as it is a lot easier to change the registration form max lengths than the MySQL max lengths later. Using smallint for age is fine. Or not. Generally you shouldn't allow for fields to take up more space than they are going to need, but I would give myself some padding just in case. Again, though, it shouldn't make much of a difference.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405