Design mistake for qa_uservotes? Separate table qa_userflags.

Question

I would like to implement the feature that: 

1. Users who flag a post must give a reason (note).
2. Users who flagged will be listed with reasons.

Now I see that we have table qa_uservotes that (by its name) should register all votes of users in the forum. However, it is also used for the flagging of posts. 

By my thinking, we should have a separate table qa_userflags that stores timestamp, postid, userid for each flag.

I am also confused about the structure of qa_uservotes, that the column "flag" is always "0". Makes no sense in my POV.

Thanks for consideration.

Answer 1

The flag field is not always 0. In fact that is exactly what keep tracks of flags. Flag a post and then that becomes a 1.

Conceptually, your nameing complaint is accurate. In fact you're suggesting normalizing a model (see here: https://en.wikipedia.org/wiki/Database_normalization). Flags are unrelated to votes so they shouldn't be in the same table. In fact, you can even smell something wrong going on when you see fields like votecreated which happens to have the name of one of the entities inside of it (a vote) because otherwise, if it was named just created it wouldn't be possible to distinguish which entity it would refer to (as it could also be a flag).

The thing with normalization is that, as a rule of thumb, it is slow. You might be wondering how having a flag in its own table could slow things down. It's simple: if you have 2 tables you need to perform 2 searches in the indexes and 2 data fetches (being the former one the heaviest). The trick here is taking advantage of the relationship between users and posts. A flag belongs to both and so does a vote. So the way of finding each of them happens to be exactly the same, which means you would have to perform the same search twice if they were split (over different sets of data, of course).

I don't like this from a design point of view but the original design had something unarguable: it didn't give either votes or flags so much reason to exist in their own. This means they were just a single field each tracking -1, 0 and 1. I give you the fact that the table uservotes was poorly named, but that must have happened because the first thing added must have been votes and then mutated to include flags and changing a table name is backwards incompatible. Now, the more fields that are added the more reason to exist in its own votes or flags have (now votes have votecreated and voteupdated dates).

Regarding the design of your plugin, the current Q2A design won't affect it negatively. Just listen to the event and track whatever information you want in your own table.

Comments

So you mean there will be no core change, and I should straight create a plugin? → "table qa_userflags that stores timestamp, postid, userid (+ NOTE reason) for each flag."

---

I have just seen that Stackoverflow Votes consist also of different kinds: "reopen closure undelete deletion downvote upvote" - so maybe a good idea after all. And yes, let's not forget that Gideon is a prodigy, he knows in >99% what he is doing :)

---

I don't know if the core will eventually change but unless someone sends a pull request for 1.8.1 I hardly believe this will be implemented soon.  Anyway, IMO it makes sense for this to be implemented as a plugin.

Even if the table name is updated, you still need to create a table for the reasons which will have the same structure you mentioned. Just don't name it qa_userflags so that it doesn't collide with a core table that might be created in the future (e.g.: qa_q2apro_userflags)