AFAIK MySQL can store BIT fields as 1 bit instead of 1 byte from version 5.1. Besides that there is a performance difference between TINYINT and INT, the latter requires four times more storage space which is especially problematic for something like the posts table that contains quite a few rows.code reader wrote:the database does not have any facility to store any field smaller than one byte, and in most database systems, there is no performance penalty up to 32 bits (i..e, reading int(8) or tinyint is not more expensive than reading int(1) (or bit), and usually it is not cheaper than reading plain int).
Another thing to take into consideration is that it's fairly hard to store bit fields and have them properly indexed. If you store them as *INT you won't be able to use an index.
In my case I've used negative values for quickdeleted posts. Besides that the system is pretty simple at the moment. A post is either:
1 : approved, everyone can view
0: not approved, only moderators can view
-1: quickdeleted, only moderators can view
In most cases users that can view unapproved posts can also view quickdeleted posts. In that case it's OK to add 'AND post_approved = 1' only if that user doesn't have these permissions.