Working With Y/n Boolean Fields In Yii/mysql

Yii 1.1 General Discussion
1

Hi folks,

I am currently in the process of building a new database which will contain many tables where a Y/N boolean field will be required.

Just wanting to know what the best practice for this is? I want to be able to have a dropdown when I am working with the database directly in Heidi. Ultimately, I guess I am simply looking for a value list of 0 or 1 for the raw dataset.

Seems like a common scenario but I cant seem to get a specific answer?

2

I used to make ENUM files in this kind of situation

3

I steer well clear of enums, they aren’t ANSI. What is wrong with:


ALTER TABLE table ADD COLUMN column tinyint NOT NULL; --use smallint in PostgreSQL

Then you can create a quick helper function to spit out Yes/No dropdowns:




public static function yesNoDropDown($model,$attribute,$htmlOptions) {

  return CHtml::activeDropDownList($model,$attribute,array(1=>'Yes', 0=>'No'),$htmlOptions);

}

But totally personal preference of course, enums are ok, I just don’t like the column enforcing values unless it is via a traditional constraint.

4

Thanks guys,

Seems like ENUMs can be a bit evil as they are not base 0 anyway and I want to return a true "0" or "1" T/F value in SQL queries, not a random integer or a string.

My problem was more with updating raw data directly in Heidi but it seems that you just have to deal with that the way it is (which isnt a huge problem as it will all be done through the web app soon anyway). As per your suggestion Luke, I will be using that as well jToggleColumn to select/deselect.

Thanks guys.