How to Structure a Trinary state in DB and Application
Posted
by
ABMagil
on Programmers
See other posts from Programmers
or by ABMagil
Published on 2014-06-09T13:37:43Z
Indexed on
2014/06/09
15:40 UTC
Read the original article
Hit count: 250
database-design
|ruby-on-rails
How should I structure, in the DB especially, but also in the application, a trinary state?
For instance, I have user feedback records which need to be reviewed before they are presented to the general public. This means a feedback reviewer must see the unreviewed feedback, then approve or reject them. I can think of a couple ways to represent this:
- Two boolean flags: Seen/Unseen and Approved/Rejected. This is the simplest and probably the smallest database solution (presumably boolean fields are simple bits). The downside is that there are really only three states I care about (unseen/approved/rejected) and this creates four states, including one I don't care about (a record which is seen but not approved or rejected is essentially unseen).
- String column in the DB with constants/enum in application. Using Rating::APPROVED_STATE within the application and letting it equal whatever it wants in the DB. This is a larger column in the db and I'm concerned about doing string comparisons whenever I need these records. Perhaps mitigatable with an index?
- Single boolean column, but allow nulls. A
true
is approved, afalse
is rejected. Anull
is unseen. Not sure the pros/cons of this solution.
What are the rules I should use to guide my choice? I'm already thinking in terms of DB size and the cost of finding records based on state, as well as the readability of code the ends up using this structure.
© Programmers or respective owner