I thought that post_vote and vote_type referred to “ordinary users voting a post Up/Down” - i.e., the popularity contest. Was your plan that this actually referred to Vote-To-Close and similar? Or that it referred to both types? I think they need to be handled separately. Probably more like:
- post_vote + vote_type == Q/A popularity. upvotes/downvotes/net/score all calculated based on post_vote + vote_type and can be recalculated (e.g., following user delete or other issues) by a simple query/join.
- separate tables for Close/Reopen/etc. Something like:
- post_action - post_id, action_type (FK to action_type reference table), status (i.e., actions may have time limits, etc. and in the end have to either “happen, causing a Close or whatever” or “discarded” or “something” - but can only get more votes while “active”)
- action_type - name, post_type (Q will have more actions than others, but some may apply to others)