New Feature: Create User Forum - December

[glenpierce]

This is a forum where users will be able to post messages to each other, we don't have a spec from design, but this is something we'll need at some point.

[av2335]

I have a few concerns about the database schema, based on the commits made so far. My apologies if any of these arise just from my own ignorance.

1. Forum post author is hard-coded. Line 65 of databaseUpdate.js reads "author VARCHAR(254) NOT NULL".

Which looks to me like we will just store the author of each post by name. For this to work takes a lot of assumptions. User names have to be unique, and almost certainly can't be updated. (If names are updated, we would need to either comb through every forum post to update the author field, or allow posts to exist that we can't match up to their author.)

The typical solution for these relationships is to use a third table to manage the relationship, which in its simplest form only needs 2 properties: postID and authorID. (the primary key of this table can then be the unique combination of these two properties).

2. The way up-voting and down-voting posts is handled has a very similar problem. The posts table has the properties "upvotes INT" and "downvotes INT". But, storing them this way makes it hard to manage things like, letting people know when they've already upvoted a thing (and preventing them from doing so again).

Also, (on line 277 of databaseUpdate.js), as it is currently written the downvote function only decrements the upvotes number. After creation, nothing at all seems to interact with the downvotes property.

All of this can be cleaned up in a similar way to the previous issue. We can make a table that just handles the upvote/downvote relationship. Probably something with three properties:
userID INT
postID INT
deltaUpvotes INT
...where deltaUpvotes would be either 1 or -1, where -1 represents a downvote, so we can just get a sum of that column to get the net number of upvotes.

3. As I look through the table creation queries, it looks like the pattern above could and maybe should be used in multiple places throughout the schema. In particular, it could clear up the relationship between location and country and site, which look a little ambiguous to me now. It could also be useful for pairing locations to indicators.

4. Should we consider having users have a hidden unique ID, instead of using their email address as the primary key? My concern is that people's emails change sometimes. In addition to which, using it as a primary key prevents us from ever allowing multiple users to use the same email. (Even if that seems fine now, who knows what future us will want to do? May as well not paint ourselves into a corner.)

5. We should probably use FOREIGN KEY where appropriate. For example, in the posts table, parent INT should almost certainly reference id in the same table.

[joshuacassidygrant]

Thanks for the feedback! I'm a bit green when it comes to database design so some of my decisions are undoubtedly naive. I can work on implementing these soon. I'm unsure of how we're handling unique user keys as of yet -- @glenpierce , were we sticking with email, or did we decide on an arbitrary unique id?

[av2335]

Oh, same here. I've only been doing computer science at all for not quite two years. This just happens to be a thing we had hammered into our heads pretty hard.