Boolean + Date Fields vs. Date Field

[DoubleDee]

Each Article on my website allows members to make Comments, however, the admin reserves the right to close commenting at any time - similar to how SitePoint does things.

From a database-design standpoint, should I…

1.) Create a “comments_open” (Boolean) field plus a “closed_on” (DateTime) field

or

2.) Just create a “closed_on” (DateTime) field and if it is Null, then my PHP code will allow commenting to continue

I guess I can see the pros and cons of each approach, but am guessing one way is better.

Thoughts?

Sincerely,

Debbie

[r937]

i loves me some NULLable columns

[DoubleDee]

@r937

i loves me some NULLable columns

Well, the reality is that “NULL” exists in the real-world quite often…

For example, hopefully in Rudy’s record, “died_on” will remain NULL for a few more years…

Debbie

[felgall]

@DoubleDee

Well, the reality is that “NULL” exists in the real-world quite often…

For example, hopefully in Rudy’s record, “died_on” will remain NULL for a few more years…

Debbie

Now you know that he is not dead so NULL is not the appropriate value for that field as that would indicate that you don’t know.

You need to be able to distinguish between unknown (NULL) and not applicable.

You might know that a person whose ‘died_on’ field is dead but might just not know the date.

You need to be able to distinguish between someone known to be dead but where the date is unknown and someone where you don’t know if they are dead.

If you use NULL then how do you know which of these three possibilities is the one that the NULL indicates?

[r937]

three? i count only two

but now, stephen, please relate your two possibilities to debbie’s situation –

1. comments are known to be closed, but not the date that they were closed?

2. comments are not known to be closed, could be open?

i still say that a single nullable column here is appropriate

[Mittineague]

I wouldn’t bother with a “close_date” unless you plan on having some kind of “automatically close after ----- days” type of thing.

[DoubleDee]

@r937

three? i count only two

but now, stephen, please relate your two possibilities to debbie’s situation –

1. comments are known to be closed, but not the date that they were closed?

2. comments are not known to be closed, could be open?

i still say that a single nullable column here is appropriate

Oh, I thought your comment above was sarcasm meaning you don’t like NULLS… (So many people in the database realm feel that way!)

Here was my original thinking…

Option #1:

- comments_open (Boolean)

Allows my PHP to have a “hook” to determine if it should allow more Comments, but doesn’t help me out if I need to know WHEN I closed Comments.

Option #2:

- closed_on (DateTime)

Allows me to know WHEN I closed Comments, but it’s always nicer when you don’t have to deal with NULLS in PHP - although that is hardly the end of the world!

Option #3:

- comments_open (Boolean)
- closed_on (DateTime)

I have a nice Boolean “hook” for my PHP code to decide if Comments are allowed. And I have a date to let me know WHEN I closed Comments. (This might be handy to study how long I manually leave Comments open before I decide to close things. And would be useful if I later automate things to close Comments, because, for example, my research shows that after 7 days, things go down-hill…)

But the downside is that I have two competing Columns with similar data.

A purist would want a trigger to ensure that if comment_open = 1 that closed_on = NULL and so on… (Almost seems denormalized!)

Follow me?

Another option could be…

Option #4:

- comments_open (Boolean)
- closed_notes (Varchar(250))

Here, I have a cleaned Boolean to work with, but I also have a place to stick in a “Date Closed” along with other info (e.g. “DoubleDee and r937 got into another tiff, so closed thread on April 5, 2014”)

Sincerely,

Debbie

[felgall]

@r937

three? i count only two

but now, stephen, please relate your two possibilities to debbie’s situation –

1. comments are known to be closed, but not the date that they were closed?

2. comments are not known to be closed, could be open?

3. known to be open - therefore a close date is not applicable.

That’s three possibilities.

All three may not apply in this particular instance but NULL is meant to indicate unknown and so specifically applies to the second of these three cases as it indicates that whether the comment is open or closed is unknown. If you use it for either case 1 or case 3 then if you reference the field in your database calls you may get incorrect results.

[DoubleDee]

@felgall

Now you know that he is not dead so NULL is not the appropriate value for that field as that would indicate that you don’t know.

You need to be able to distinguish between unknown (NULL) and not applicable.

You might know that a person whose ‘died_on’ field is dead but might just not know the date.

You need to be able to distinguish between someone known to be dead but where the date is unknown and someone where you don’t know if they are dead.

If you use NULL then how do you know which of these three possibilities is the one that the NULL indicates?

I hear what you are saying, but I think it depends on context…

If I had a field called gender that was char(1) and Nullable and it held no value, then you’d be left wondering…
a.) Was this person in an accident?
b.) Is this person shy?
c.) Did this person miss this field?

But with a DATETIME field, I think it is pretty common to make them Nullable, and it is understood that NULL means n/a

(Almost every table in my database has a “created_on” and “updated_on” field in it, and when a new record is added, “updated_on” remains NULL until the record is later changed…)

Sincerely,

Debbie

[DoubleDee]

@felgall

Now you know that he is not dead so NULL is not the appropriate value for that field as that would indicate that you don’t know.

You need to be able to distinguish between unknown (NULL) and not applicable.

You might know that a person whose ‘died_on’ field is dead but might just not know the date.

You need to be able to distinguish between someone known to be dead but where the date is unknown and someone where you don’t know if they are dead.

If you use NULL then how do you know which of these three possibilities is the one that the NULL indicates?

Hey Felgall…

Ironically, the MySQL Manual has an example dealing with death…

http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Off Topic:

See @r937 ;, I do read the Manual now and then!!

Sincerely,

Debbie

[r937]

@DoubleDee

Off Topic:

See @r937 ;, I do read the Manual now and then!!

i am sure you do, but it’s the times you don’t that can be exasperating