Worried about Data Types (mySQL)

nickrigby

Hi all,

I'm having one of those horrible dilemas, when setting up a mySQL database. There are so many data types to choose from these days, I'm unsure of which ones to choose. Obviously I want to choose the most accurate and efficient type for each field.

My main concern is for fields that will contain text. There are so many options, varchar, text, blob etc.

I am setting up a customer database with name, address, telephone etc. I would be swayed towards using Varchar for them all. Would this be right?

Also, would you use Varchar or a numeric field for telephone number?

Thanks,

Nick.

nickrigby

Sorry.

Additionally, what's the best method of storing a true or false value?

Thanks.

Roger_Ramjet

Yes, use varchar for things like name and address fields, then you can allow for long names or address lines and still store them efficiently. Most people have names that will fit into 20 chars but you must allow for someone with a name that takes up 30 or 40. Also, you can allow for extra address lines, just in case, but not waste space when most addresses don't use the last line.

Also you should use varchar or text for all numbers that cannot be manipulated mathematically. A phone no is a number but you don't want to add them together accidentally. Likewise things like account nos or invoice nos. If the script starts treating them like real numbers you will get some funny results.

Use bool, or boolean for storing TRUE and FALSE. (The convention is that boolean is a signed tinyinteger with 0 = true and -1 = false.)

nickrigby

Fantastic.

Thanks.

Is there a big difference between Varchar and Text?

Roger_Ramjet

Only one:
varchar can only hold 255 characters max : text can hold a lot more (@64K)

Both are variable length data types, so your choice of which one is down to the max length of the string you will store in it.

See here for data type docs:
http://dev.mysql.com/doc/mysql/en/Column_type_overview.html