DB Naming convention - Round 2

  • Table and column names will be snake_case. Postgres assumes identifiers are lower case unless specified otherwise.
  • Table names will be singular.
  • Column names will be singular or plural depending on what makes the most sense.
  • Table and column names should not be reserved words or include special characters.
  • Postgres adds columns to the end of the table, so the first 5 columns that every table will have are.
    • The primary key will be named “id” and will be the first column in the table. The datatype will be bigserial.
    • The second column will be called create_date and will default to the date the row is created.
    • The third column will be called last_modified_date will auto update whenever the row is updated
    • The fourth column will be called created_by_member_id and will be the id of the member that created it.
    • The fifth column will be called last_modified_by_member_id and will be the id of the member that last modified it.
  • Foreign keys will follow the format table_id and the data type bigserial.
  • Boolean columns will start with either “is” or “can” such as is_suspended or can_upvote.
  • Join tables will be the table names squished together and snake_cased such as post_vote unless it would be more clear not to.
  • Tables with soft deletion will have the following 3 fields.
    • deleted_date
    • is_deleted
    • deleted_by_member_id
  • Date fields should have the word “Date” in them like suspension_end_date
  • In case we need a foreign key to reference to the table it is in, the column name will be parent_id.

I like the conventions.

And deleted_by_user_id at a guess?

And given create_date (or created_date?) then created_by_user_id might be semi-standard also, ditto modified_by_user_id.

1 Like

Good points, updated

The one who dealt the death blow? What about deletions by community vote? If we want to show (or audit) that info like on SE, we’ll need to have all the member IDs somewhere. This will come up for close votes too. (Does Postgres have arrays of primitive types?)

1 Like

@cellio This is really not a problem. Things like deleted_by_member_id is not to “assign blame”. It is to trace actions in case there are issues. So for this (and similar for others):

  • We will have “magic/fake members” for various system functions that are actually “real people directly” - e.g., “close-vote-processing-system”, “spam-auto-delete”, “monthly-database-cleanup-process”, etc.

  • For something that requires multiple people - Close, Reopen, etc. those are essentially “votes”. Not exactly the same as normal Post votes, and therefore stored in a separate table something like:

  • post_action_vote

    • post_id
    • member_id
    • action_type (Close, Reopen, Delete)
    • vote_type

Might get a little more complicated - need to distinguish Initial Close vs. Second Close after Reopen. So it might really be 2 tables - one for the action and another for the votes.

I’m not worried about assigning blame; I just want to make sure that we’ll be able to show “deleted by Larry, Curly, and Moe” if that’s what happened. That’s distinct from “deleted by spam flags” and the like.

One row per event, and record timestamps. (And vote retraction is another event type and another row.)

There is already the vote_type table and the post_vote table, is there a reason that Close and Reopen couldn’t be added to the post_vote table?

1 Like

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)

As I originally intended it was for all the different vote types, upvote,downvote/closevote/reopen/accept.

Whether one table or two, vote_type it needs more detail. Right now it just has name.

A suggestion: maybe use Serial type for ids of lookup type tables? Table: time_of_day: values (‘am’, ‘noon’, ‘pm’, ‘midnight’) doesn’t want a BigSerial id (in fact SmallSerial would be the most appropriate type)