yes and no values

[OriginalSunny]

Hi,
I have created a table which has many fields all of which are 'yes' and 'no' values. (i.e for a mobile phone it either has a camera or not, therefore the field camera will either have a value of 'yes' or 'no' assigned to it).
The other thing is when i then output the value it should simply say 'yes' or 'no'.
Should i store the camera field simply as a varchar(5) and assign 'yes' or 'no' to it or is there a better way of doing it??
I read somewhere that there is such a thing as:

[i]enum('yes','no')[/i]

Not sure what that does but would it work for what i want to do??
Thanks.

[obsidian]

i have found that the best way to record values like that is a char(1) fields with either a 'y' or 'n' in it. the reason i like this method is that in cases where i couldn't imagine adding another option, i've often had to add a third option, and if you keep it with char(), you can use any letter or character you want to represent your options.

again, a lot of DB design is preferential, but this is definitely a method that has been extremely useful to me.

if you're absolutely certain you're not ever going to have to represent a third value in the column, you could always save tons of space with using an int2 and simply storing a '0' for no and '1' for yes.

hope this helps

[fenway]

You could be super-efficient and simply use a BIT(1) column -- ON/OFF -- but that's not really the point. Others may argue, but IMHO, it's very rare that you _never_ need to store anything else both on/off, true/false, active/inactive, etc. There's always going to be 3rd, and probably 4th option at some point in the future.

Obviously, ENUM columns save space, but otherwise, they're cumbersome -- it's annoying to try and get back all of the possible ENUM options, and so on. Besides, how much does space really matter here? You can have a char(20) column, fixed width, instead -- so it adds hundred bytes to each row. Big deal! Why be stuck with a cryptic single character abbreviation?

I've moved away from ENUMs entirely -- it's annoying to have to change the DB design simply to add another option, and there really is no other benefit other than storage and slightly better performance on lookup. But the column would be indexed anyway, right?

[wickning1]

Reducing storage cost is not just about hard drive space!

First, on indexed columns saving space is very important because you want to keep as many indexes in main memory as possible. As your database grows, more and more indexes will be forced to reside on the disk during run time, which can severely hamper performance.

Second, disk reads are the most time consuming operation in a database system. If you optimize for storage and reduce your static row length by 50%, operations on that table will happen twice as fast.

Sure, lots of little systems will never reach any kind of performance limitation, but that's no reason to ignore performance completely.

Now, for the topic - if your application is checking $var == "yes" in 5 million places then it's not reasonable to change the column to char(1) or bit or tinyint. What is reasonable is enum. You get the performance of tinyint without having to change code. The column acts exactly as if it were varchar, but you can only put in "yes" or "no". If you ever need to add another value, you alter the column so that it supports it.

Personally when I store booleans I use tinyint 0 or 1.

[fenway]

[!--quoteo(post=355517:date=Mar 15 2006, 06:36 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 15 2006, 06:36 PM) [snapback]355517[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Reducing storage cost is not just about hard drive space!

First, on indexed columns saving space is very important because you want to keep as many indexes in main memory as possible. As your database grows, more and more indexes will be forced to reside on the disk during run time, which can severely hamper performance.
[/quote]
Agreed -- but I doubt using a char(10) vs enum would make any meaningful difference when most people happily create indexes of all 255 characters of a CHAR field, when a prefix of 10 would suffice. You have to pick you battles -- I'd rather not sacrifice ease of development for a few KB of index size. If index caching was an issue, there are plenty of unnecessary indexes in most peoples' tables anyway.

[!--quoteo(post=355517:date=Mar 15 2006, 06:36 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 15 2006, 06:36 PM) [snapback]355517[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Second, disk reads are the most time consuming operation in a database system. If you optimize for storage and reduce your static row length by 50%, operations on that table will happen twice as fast.

Sure, lots of little systems will never reach any kind of performance limitation, but that's no reason to ignore performance completely.
[/quote]
Agreed -- but 50%? First, fixed-widths tables are required for this to be valid, and many people use VARCHARs all over the place. And BIGINT(20) when INT would suffice -- and there are many, many for FK fields than ENUM/VARCHAR flag fields! Using CHAR(10) instead of ENUM is marginal at best -- there are many other (better) places to improve row size, again, all possible without sacrificing convienience on this one column. We're only talking about an extra 8 (10-2) bytes here -- let's be reasonable.

[!--quoteo(post=355517:date=Mar 15 2006, 06:36 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 15 2006, 06:36 PM) [snapback]355517[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Now, for the topic - if your application is checking $var == "yes" in 5 million places then it's not reasonable to change the column to char(1) or bit or tinyint. What is reasonable is enum. You get the performance of tinyint without having to change code. The column acts exactly as if it were varchar, but you can only put in "yes" or "no". If you ever need to add another value, you alter the column so that it supports it.

Personally when I store booleans I use tinyint 0 or 1.
[/quote]
Yes, ENUM is convienient, yes, it's a specialty data type, yes, it saves storage space. No, it's not worth having to ALTER your tables just to add a 9th value to your ENUM, when doing it in your app code makes more sense. No, the default value isn't intuitive. No, ordering by ENUM vs. ENUM+0 isn't as wonderful as it sounds.

Don't get sucked in -- there are many considerations here -- and you'll be stuck with them for the lifecycle of your DB.

And with all due respect to wickning1 (who is, btw, is a proven expert on these forums without a shadow of a doubt), I think you'd need to use a benchmark tool to demonstrate even a minor performance penalty of the above in real-world situations.

I expect a flurry of responses -- I too have gone the ENUM route many a time, and have lived to regret it each and every time without fail.