Article Status

DoubleDee · May 10, 2014, 4:48pm

I have decided that there needs to be a way to denote whether an Article is active or not.

This is important, because if an Article is not active, then it should not be able to be displayed on my website, and users should not be able to add comments to it.

From both a Database and Business standpoint, how easy or complicated should I make such a column?

Here are some different ways to approach things…

Option #1:

status {0, 1}

Option #2:

status {'active', 'not active'}

Option #3:

status {'active', 'not-active'}

Option #4:

status {'active', 'retired-yesterdays-news', 'retired-lame'}  <=====  Just trying to make a point!  ;)

Option #5:

active_status {0, 1}

Option #6:

active_status {FALSE, TRUE}

Option #7:
Something else???

For the time being, I can see an Article being taken offline because it is “ancient history” or because it was poorly written. (Yes, there are some articles that I wrote a few years ago, that I would not want published on my “new & improved” website!!)

Thoughts?

Sincerely,

Debbie

r937 · May 10, 2014, 6:47pm

pick one

they’re all basically the same

the use of TINYINT will save you a bit of space and a wee dollop of processing time (but i doubt it would be measurable)

DoubleDee · May 10, 2014, 6:59pm

1.) Is there a way to store a true - no pun intended! - Boolean in MySQL?

2.) If I stored values as a TinyInt, can I write my MySQL queries like…


SELECT....
:
WHERE active = TRUE

(I don’t like storing or coding against 0 and 1, because when you look back at things, it isn’t always obvious what you are referring to!)

Debbie

r937 · May 10, 2014, 11:29pm

well, yes and no

i invite you to read the entry under BOOLEAN on this page in da manual

see, i even looked it up for you

what happened when you tested it? ™

John_Betong · May 11, 2014, 12:10am

I would go for option 4 and save the status as a single character. I am also not a fan of 1 or 0.

DoubleDee · May 11, 2014, 12:50am

I thought they were adding TRUE and FALSE into MySQL this lifetime like normal databases?!

Just because something appears to work, doesn’t mean it is right…

I guess the Manual does spell out what happens in MySQL, but I was also asking about MySQL queries in my PHP scripts…

Debbie

Mittineague · May 11, 2014, 1:02am

I don’t have trouble reading 0 as negative and 1 as positive. It’s very common.

Some confusion might come if you are taking it out of context. For example, in arrays 0 means first.
So indexOf == 0 is “true” (postition one) and == -1 means false.

In any case, I don’t think using true false (or maybe T F) instead would make any negligible difference if you’re not interested in squeezing out every last bit of optimization possible.

John_Betong · May 11, 2014, 1:13am

The problem I have is echoing a 1 or 0 result. Former OK but latter does not show anything

Most important is that using a single character allows multiple choices.

Mittineague · May 11, 2014, 1:18am

I think something like CHAR(1) with either T or F would be fine.
Even if you had a zillion users at the same time what diference would it make? .000987 seconds?

True, using a string datatype for something that’s essentially numeric is a “waste” but I doubt it makes much difference pragmatically.

@John. I must be missing something

<?php
$val = 0;
echo $val;
?>

displays a 0 for me.

DoubleDee · May 11, 2014, 1:21am

Except that creates a new problem…

‘t’, ‘true’, ‘T’, ‘True’, and ‘TRUE’ would all be evaluated as TEXT in both MySQL and PHP, right?

In a truly “Enterprise” database, 1 and TRUE would both represent the positive Boolean, and you could store 1 or TRUE in the column and the database engine would know what you mean.

If you store a ‘T’ in a TinyInt, or ‘TRUE’ in a CHAR(4) you lose the Boolean effect…

I agree that 0 and 1 should be pretty intuitive to a geek like me, HOWEVER, I have a TinyInt field called “gender” where…


0 = unknown
1 = male
2 = female
9 = n/a

So if you just look at the values in a TinyInt field, you don’t know if it is Boolean (binary), ternary, or decimal. You also don’t know what the values represent at face-value which was my original point…

Since r937 is saying that 0 = FALSE and 1 = TRUE, I guess it would be easiest to create a field called “active” in my “article” table and have 0 = FALSE, 1 = TRUE…

I just find it a PITA you can’t have a field of type “Boolean” and store TRUE and FALSE in the actual field so it is clear it is showing Boolean values, as opposed to guessing if TinyInt is for a tiny number or a Boolean…

Sincerely,

Debbie

John_Betong · May 11, 2014, 1:27am

Saving the result as a single character is more intuitive,

U, m, f, n.

Debbie, am I on your blocked list?

Mittineague · May 11, 2014, 1:32am

Off Topic:

What have you done now John?

I don’t know what you mean by “boolean effect”. Maybe you’re looking for NULL vs. “value”?

DoubleDee · May 11, 2014, 1:55am

What’s that?!

Not that I know of…

Debbie

DoubleDee · May 11, 2014, 2:04am

A Boolean is supposed to be a CONSTANT, and most enterprise databases like Oracle use {TRUE, FALSE} but recognize {0, 1}.

MySQL chooses to use TinyInt as the data-type for Boolean, and while it uses {TRUE, FALSE} as aliases in queries, it would let you store {TRUE, FALSE} in a field so that it is readable by both human and database engine.

And if you store a {TRUE, FALSE} in another data-type, it will be human recognizable but MySQL would balk and treat the values as TEXT.

That’s what I was getting at.

Debbie

John_Betong · May 11, 2014, 3:06am

Here is an example which explains what it is:


<?php 
// KLUDGE to simulate database field values
   $id_post = (object) 'Active';
   $dataBaseField = (object) 'Fred'; 
   $dataBaseField->f = 'f';
   $dbField = array('m' => 'm','f' => 'f');
 
// Constants applicable to all database single character fields
   $id_post = array
   (
    'M' => 'Maybe again',
    '0' => 'false',
    '1' => 'true',
    '0' => 'false',
    '2' => 'maybe',
    '3' => 'possibly',
    'a' => 'active',
    'f' => 'female',
    'j' => 'Just trying to make a point',
    'L' => 'Lame',
    'm' => 'male',
    'n' => 'n/a',
    'N' => 'News',
    'R' => 'retired',
    'u' => 'unknown',
    );

echo '<br />// Output';

echo '<br /><br />// Using Constant - $id_post["m"]';
  echo '<br />Status: ' .$id_post["m"];

echo '<br /><br />// [COLOR=#737373][FONT=Source Code Pro][COLOR=#336699]mysql_fetch_array(...) [/COLOR][/FONT][/COLOR] -  $id_post[ $dbField["m"] ]';
  echo '<br />Status: ' .$id_post[ $dbField["m"] ];

echo '<br /><br />// [COLOR=#336699][FONT=Source Code Pro]mysql_fetch_object(...) [/FONT][/COLOR] - $id_post[$dataBaseField->f]';
  echo '<br />Status: ' .$id_post[ $dataBaseField->f ];


?>

// Output

// Using Constant - $id_post[“m”]
Status: male

// mysql_fetch_array(…) - $id_post[ $dbField[“m”] ]
Status: male

// mysql_fetch_object(…) - $id_post[$dataBaseField->f]
Status: female

Mittineague · May 11, 2014, 3:47am

@John, the fact that Debbie didn’t recognize U = unknown, m = male, f = female, and n= n/a as abbreviations suggests she should probably go for someting a bit more verbose - not as elegant or efficient perhaps, but more readable anf memorable.

Do you see any potential problems with having “inactive” as NULL (as opposed to FALSE) until set?.

John_Betong · May 11, 2014, 4:11am

Mittineague:

I think something like CHAR(1) with either T or F would be fine.
Even if you had a zillion users at the same time what diference would it make? .000987 seconds?

True, using a string datatype for something that’s essentially numeric is a “waste” but I doubt it makes much difference pragmatically.

@John. I must be missing something
<?php
$val = 0;
echo $val;
?>
displays a 0 for me.

Try this:



$status = array(true, false);
foreach($status as $statii)
{
  if($statii)
  {
    echo '<br />True: '.$statii;
  }else{
    echo '<br />False: '.$statii;
  }  
}


// Output
   True: 1
   False:

John_Betong · May 11, 2014, 4:26am

The Original Post asked about “status” database storage and Case: 4 was outside the boolean scope. Later tinyint was suggested for storage which takes the same amount of space as a single char field. The latter could be used as a “SLA”, Single Letter Anachronym which I find more flexible, intuitive and database output can also easily be changed by using the $id_post array item value. Plus an added feature of adding CSS styling.

// Constants applicable to all database single character fields
$id_post = array
(
‘f’ => ‘female’,
‘j’ => ‘Just trying to make a point’,
‘L’ => ‘Lame’,
);

Revised:
// Constants applicable to all database single character fields
$id_post = array
(
‘f’ => ‘female’,
‘j’ => ‘<b style=“color:red”>This is the point I am trying to make :)</b>’,
‘L’ => ‘Lame’,
);
[

I agree that setting “active” default as NULL which will not only be recognised as PHP False but also err on the safe side of being incorrectly classified.

r937 · May 11, 2014, 8:42am

actually, that was the first suggestion

for the simple reason that mysql doesn’t support true (no pun intended) booleans

i’m not a fancy-schmancy php developer like all you guys with your arrays and echos, but i’m pretty sure that 0 and 1 are reasonably ubiquitous, enough so that no one should have any doubts as to what they mean

however, if there is any possibility of doubt, then you should use a foreign key instead of just a column with some predetermined values

Mittineague · May 11, 2014, 3:43pm

I like that. That would allow for any changes that might later happen.

truth_tokens
id . value
0 . False
1 . True
3 . Schrödinger