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: 324
        
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 trueis approved, afalseis rejected. Anullis 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