Database Normalization Causing Inefficiency

by turkeypenguin   Last Updated January 14, 2018 08:06 AM

Note: I am relatively knew to managing an SQL database. I have done my fair share of reading, research, and experimentation, but it is possible that I am missing some fairly basic knowledge. I'm also not sure how to tag this question so if someone could add relevant tags that would be appreciated.

I am currently building an SQL database for a forum like website. Among other things, the website will contain pages with threads, basically a series of posts by different users.

Users on the website are identified by a unique ID (char(36)) that is set once at registration and cannot be changed afterward. The user also has a username (varchar(16)) that is just a screen name. This name is almost always unique between users, but not necessarily. The user can also change the name, but there is a hard limit of two weeks between any two username changes. All of this information, and a few other things like login details, are stored in one Users table with the ID as a primary key.

There are multiple tables for the forum itself, but the one that is important for this question is the Posts table (primary key is 'post_id' int(10) NOT NUll AUTO_INCREMENT PRIMARY KEY. It contains one row for every time somebody makes a post on a thread, including responses to existing posts. As a result, whenever a user requests a thread, I query the posts table for all posts matching that thread (I store a thread ID foreign key). My question now concerns storing a user who is the author of the post. When a thread is requested, I need to send the username (not ID) of the users that made different posts. However, I also need to make sure that changes in usernames do not cause the apparent author of a post to change. As far as I can tell I have two possibilities:

  1. For storing the author of a post, I can store only the ID. However, this means that I will have to make quite a few requests to the Users table for the usernames of the various IDs of the posters. This seems highly inefficient, seeing that the usernames will change very rarely.

  2. I can store both the author ID and the author username in the Posts table. I can then go through the table and change all usernames among the posts (I can even set this up with a trigger, I believe). However, this opens the window for update anomalies, if the username stored for an ID in the Users table is different than in one of the posts. I can still use a trigger to prevent these anomalies at the database level, but from what I understand this should not be necessary in a well-designed database.

Which one of these two solutions (or something entirely different) should I use to both maintain efficiency and database normalization?

If it matters, I'm using the most recent version of MariaDB and accessing using PHP.

Tags : performance

Related Questions

Lower performance on newest servers

Updated August 29, 2017 14:06 PM

poor and/or unreliable InnoDB performance

Updated June 21, 2017 04:06 AM

How MySQL use the InnoDB Buffer?

Updated July 13, 2018 05:06 AM