checkbox values stored in mysql

[Xenophobe]

I have a series of checkboxes and need to store their values in a MySQL
table.

I'm wondering what data type people recommend:

varchar or text, exp. "True/False", "Yes/No"
tinyint, exp. "0/1"

Maybe it depends on the usage or maybe it doesn't matter and/or is a choice
of personal preference.

Comments welcome.

p.s. Is there a reference page somewhere that discusses the various data
types and their recommended uses?

[Michael Austin]

Xenophobe wrote:

I have a series of checkboxes and need to store their values in a MySQL
table.

I'm wondering what data type people recommend:

varchar or text, exp. "True/False", "Yes/No"
tinyint, exp. "0/1"

Maybe it depends on the usage or maybe it doesn't matter and/or is a choice
of personal preference.

Comments welcome.

p.s. Is there a reference page somewhere that discusses the various data
types and their recommended uses?

True/False 0/1 will basically be personal preference - and it is a lot more
efficient to store 1 byte (tinyint) vs. 5.

--
Michael Austin.
Consultant - Available.
:)

[Xenophobe]

I tend to agree.

Speaking of efficiency, do you prefer to use varchar? I've been told in the
past to treat all data as text UNLESS it's a number that requires
calculation.

"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:Zo*****************@newssvr22.news.prodigy.co m...

Xenophobe wrote:

I have a series of checkboxes and need to store their values in a MySQL
table.

I'm wondering what data type people recommend:

varchar or text, exp. "True/False", "Yes/No"
tinyint, exp. "0/1"

Maybe it depends on the usage or maybe it doesn't matter and/or is a choice of personal preference.

Comments welcome.

p.s. Is there a reference page somewhere that discusses the various data
types and their recommended uses?
True/False 0/1 will basically be personal preference - and it is a lot

more efficient to store 1 byte (tinyint) vs. 5.

--
Michael Austin.
Consultant - Available.
:)

[Andy Hassall]

On Tue, 13 Jul 2004 15:54:00 GMT, "Xenophobe" <xe*******@planetx.com> wrote:

I have a series of checkboxes and need to store their values in a MySQL
table.

I'm wondering what data type people recommend:

varchar or text, exp. "True/False", "Yes/No"
tinyint, exp. "0/1"

Maybe it depends on the usage or maybe it doesn't matter and/or is a choice
of personal preference.

Largely comes down to personal preference, I think.

I use 'T'/'F' mainly just because that's the convention at the company where I
work.

0/1 has the advantage that you can use the value as a Boolean value in PHP or
Perl etc.

I wouldn't use anything longer than a byte, though, so that rules out the
wordier 'Yes'/'No' 'True'/'False' options.

In fact in Oracle, which I mostly work with, 1 is two bytes in its internal
storage scheme, whereas 'T' is one byte (unless you're using UTF16 as the
database character set).

MySQL's TINYINT type guarantees a single byte for the number though.

Should be wary of trying to be clever and forming bitmasks in a single column
just to start saving bytes, since you're breaking normalisation rules if you go
down that route.

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

[Michael Austin]

Xenophobe wrote:

I tend to agree.

Speaking of efficiency, do you prefer to use varchar? you need to review the MySQL docs concerning the CHAR/VARCHAR data types at:

http://dev.mysql.com/doc/mysql/en/CHAR.html
and
http://dev.mysql.com/doc/mysql/en/Si...n_changes.html

There is a table that shows what the difference between CHAR and VARCHAR - read
them carefully. If you don't understand, just ask.

I've been told in the
past to treat all data as text UNLESS it's a number that requires
calculation.

<snip>

Whoever gave you that sage advice obviously should not be designing databases.
There are many different reasons to use different datatypes. The least of which
is efficiency. Yes, I did mention that before, but I also took into account
what you were trying to achieve.

<RANT mode=annoyed>
One of my pet peeves is the fact that too many programmers are creating
"databases" (and I use the term very loosely here), and then can't understand
why their database doesn't perform. Sometimes the problem is also the database
engine they have chosen to employ. MySQL and PostgreSQL are fine for some
things, but may not be "enterprise class". And expecting an interpreted
language like PHP or Python etc... to be scaldingly fast is just asking for
trouble. wheeew...
</RANT>

--
Michael Austin.
Consultant - Available.
OracleRdb, OracleRDBMS, MySQL DBA and OpenVMS, Linux System Administrator
Web Administrator and Generalist
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)