Are boolean flags in a database acceptable for this list of options?

Question

I'm working on adding user submitted comments to a personal project.

Comments have a number of flags for special purposes. Comments are also versioned for moderation purposes so timestamps aren't need on specific fields.

These are the options I have so far in the functional design. (list not final) This list will change at some point because I know I haven't figured out everything.

• Wrapped (The text is hidden behind a javascript link)
• Hidden (The text of comment is hidden from non-moderators)
• Locked (Can't be edited)
• Deleted (Comment deleted)
• Moderator (hides the name of the poster)
• Administrator (same as moderator except moderators can't edit)
• Hellbanned (Acts deleted except for the poster)

There are some rules governing what flags can exist together. (As far as display is concerned.)

• Administrator/Moderator comments shouldn't be wrapped, hidden, locked, or hellbanned. (Moderators can't be hellbanned)
• A comment can't be an Administrator and a Moderator comment at the same time.
• Hellbanned shouldn't have Deleted.
• A comment cannot have both Wrapped and Hidden
• Hidden comments should be Locked.

Are boolean flags enough to implement this in a database? (I'm using Ruby on Rails with SQLite/MySQL so the database can't have triggers/constraints)

Answer 1

You could implement these either as boolean flag columns, or have an int column that acts as a bitmask. Some might argue (and they'd technically be correct) that a bitmask in the database violates normalization (in that more than one "value" is stored in a single column), but I've found them useful in the past. The only issue is that it can make the code less readable.

In the end, though, I would say there's nothing wrong with your approach.

Answer 2

For the rules, it seems like you have two options:

1. You can try and set up Constraints and triggers to enforce that no bad data can ever get -into- the database
2. You can ensure in your app code through Pre and Post Conditions on your functions (not necessarily coded pre and post conditions, but explicit ones defined and adhered to) to make sure they don't introduce bad data. Then you can run Bad Data scripts regularly to alert you to anything that violates the rules.

You're going to need to do some of #2 anyway, unless you plan to raise exceptions in the DB and handle them client-side (which smells bad to me...) but I think I'd be hesitant to do #1 unless you covered -all- the cases, because if you didn't you'd have some enforced there and some not and that also smells bad to me...

Answer 3

Here's the problem with boolean flags: They only have two states.

Many things turn out to have multiple conditions and a simple boolean turns out to be inadequate.

Some things may actually be boolean. This is rare, however.

I suggest not using boolean, but instead using some kind of enumeration of values.

• Wrapped (The text is hidden behind a javascript link). Looks boolean: there's a link or there is not a link. Sadly, you may have different kinds of links (internal, external, etc.) so this may grow beyond two values.

• Hidden (The text of comment is hidden from non-moderators). Looks boolean -- hidden or not. Except, of course, the definition of non-moderator might expand, and then this becomes a much more complicated thing.

• Locked (Can't be edited). Looks boolean, locked or open. Except that you may have locked from one class of users but not locked from another class of users. Boolean breaks down.

• Deleted (Comment deleted). Boolean until you add pending delete, or needs another vote for deletion.

• Moderator (hides the name of the poster). Boolean until you have other classes of users, super users, more privileged users, and less privileged users.

• Hellbanned (Acts deleted except for the poster). This appears to be a feature of the author of the comment, not the comment itself. This doesn't even belong here, but belongs elsewhere in your model.

I vote for enumerated values rather than simplistic True/False.