checkbox values stored in mysql

**Michael Austin** · Jul 17 '05, 07:09 AM

Re: checkbox values stored in mysql

Xenophobe wrote:[color=blue]
> 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?
>
>[/color]

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** · Jul 17 '05, 07:10 AM

Re: checkbox values stored in mysql

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" <maustin@firstd basource.com> wrote in message
news:ZoXIc.2059 $wh3.1005@newss vr22.news.prodi gy.com...[color=blue]
> Xenophobe wrote:[color=green]
> > 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[/color][/color]
choice[color=blue][color=green]
> > of personal preference.
> >
> > Comments welcome.
> >
> > p.s. Is there a reference page somewhere that discusses the various data
> > types and their recommended uses?
> >
> >[/color]
>
> True/False 0/1 will basically be personal preference - and it is a lot[/color]
more[color=blue]
> efficient to store 1 byte (tinyint) vs. 5.
>
> --
> Michael Austin.
> Consultant - Available.
> :)[/color]

**Andy Hassall** · Jul 17 '05, 07:10 AM

Re: checkbox values stored in mysql

On Tue, 13 Jul 2004 15:54:00 GMT, "Xenophobe" <xenophobe@plan etx.com> wrote:
[color=blue]
>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.[/color]

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 <andy@andyh.co. uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

**Michael Austin** · Jul 17 '05, 07:10 AM

Re: checkbox values stored in mysql

Xenophobe wrote:
[color=blue]
> I tend to agree.
>
> Speaking of efficiency, do you prefer to use varchar?[/color]
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.

[color=blue]
>I've been told in the
> past to treat all data as text UNLESS it's a number that requires
> calculation.
>[/color]
<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
:)