2

I am working on a table for a registration form. Among it's fields/columns are:

student_no (primary key) and age

The student_no would hold values that have a fixed length of 9 characters that contain 8 numbers and a dash, for instance 11-423685, 12-537859, 12-974156

I'm not sure whether to use char or varchar because I wanted to know more about the two types before using them. specifically:

  1. Can one or both of these types hold values that include various symbols on the keyboard (-, @, _, $, #, etc.), like how I need the student number to contain a dash?

  2. If I give char a length of 9 would it be invalid to give it a value of length 8 and below

  3. Other important differences to note, if any

as for the age, do i need to specify a length if I use tinyint? or should i just leave the length field in phpMyAdmin blank?

note : this is actually just a school exercise where the instruction was to make a working registration form with any fourteen fields, so I decided to not use a birthday for now to avoid having to use dates.

ransan32
  • 225
  • 2
  • 3
  • 9
  • All your answers are answered here ....http://dev.mysql.com/doc/refman/5.0/en/char.html – Mudassir Hasan Dec 02 '12 at 02:08
  • Regarding your comment in your [deleted question here](http://stackoverflow.com/q/13902078/425275), `
    `, and `` are empty elements, so they don't need an end tag, or a `/`; just write them as I wrote them here. The doctype you should use is ` `.     – Šime Vidas Dec 16 '12 at 16:05

2 Answers2

2

INT doesn't allow any non-numeric formatting. VARCHAR does.

However, it's still better to use an INT to represent the student number, as INTs will result in faster searches.

You can use formatting to present the student number with a hyphen on output. The database should store it in its most efficient format (an unformatted number).

Also, I suggest you use a DATE to store the birth_date, rather than a numerical age. This will prevent issues with changing ages as time goes on. Again, you can calculate age on output for presentation.

Steven Moseley
  • 15,871
  • 4
  • 39
  • 50
  • so if I need to include the dash, I should use varchar? – ransan32 Dec 02 '12 at 02:01
  • Again, you can remove the dash prior to saving the number, and add it back after retrieval, which is what I suggest. But if you insist on a hyphen in the database, yes, you have to use a varchar. – Steven Moseley Dec 02 '12 at 02:02
  • Or, consider storing the 11 and the 423685 in separate columns if possible, and especially if they have semantic meaning. – jmilloy Dec 02 '12 at 02:10
  • @jmilloy Good point, but I would say **only** if they have semantic meaning. A single int would be the best performing option. – Steven Moseley Dec 02 '12 at 02:14
2

CHAR() is weird. It's a fixed-length string, and is padded with spaces if you try to set it to a shorter value. It is almost never appropriate to use -- if you forget it ever existed, you will probably never miss it.

VARCHAR() works largely how you'd expect. It can contain any string (with any characters) up to its maximum length.

Don't store age. Store birth dates (using a type like DATE). But if you must store an age, the length on numeric types (like TINYINT) is pretty much meaningless, and should be left blank.