Database Normalization Causing Inefficiency Note: I am relatively new 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:


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.
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.
With regard to the threading issue that people have picked up on as much as your question about user id and name normalisation, without using CTEs there are a number of ways to arrange hierarchies to allow efficient retrieval of threads or portions thereof. You could store a materialised path to each post for instance. Efficiently dealing with trees in SQL is a subject whole books could be (and have been) written about, so I'll not go into more detail here. For a quick primer the relevant chapter in Bill Karwin's "SQL Antipatterns" (a book I recommend generally) is a good place to start, or for a fuller analysis Joe Celko's "Trees and hierarchies in SQL for Smarties", or search online for the tree management methods (with search terms like adjacency list, materialised path / cached path / path enumeration, nested sets, and of course recursive CTE / recursive common table expression).

On to the actual question:


  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.


This is normal and should not be significantly inefficient. You will not be performing a separate lookup for every user ID spotted, instead you will use a JOIN and let the SQL engine worry about that for you which it will do well if you give it good index choices to work with. Something like:

SELECT posts.postTitle
     , posts.postText
     , posts.postTimeStamp
     , users.fullName
FROM   posts
JOIN   users
       ON users.id = posts.userID


Having said that, it is important to note that data normalisation is not about performance efficiency it is about protecting data integrity/correctness and (less importantly) storage efficiency: in this instance if you avoid having several copies of the same data (the username) you avoid the possibility of inconsistency because one or more copies disagree with the others.


  I can store both the author ID and the author username in the Posts table. 


Unless you want to allow usernames to change and keep the original name displayed, this is unnecessary and you should use normal form and a join, as above.

Before trying to support name changes in a particular way, first decide if you actually need to allow them at all. If the name in question is an account name then perhaps you don't need or want to support it changing at all. If it is a personal name or email address, both of which can change over time, then you perhaps want to change all instances when this happens so you don't have to worry about it: just update the name in the users table and future queries like the above will get the new data instead of you needing to


  go through the [posts] table and change all usernames among the posts 


at all.

Things get a bit more complicated if you want to maintain the display of the user's name at the time they made a post instead of old posts being displayed with the new name. You could store "name at time of post" in the posts table, and with this requirement you don't need to update it when the user's name changes (you are wanting to keep the old name). The other option is to keep a history of the user's record in normal form and using that to ready the correct name. This will be more efficient for storage (and less likely to become inconsistent due to bugs) but reading the username-at-time-of-post can be more complicated and will vary depending on what your chosen database supports (does it have efficient window functions?) and how you chose to store the history (using "system versioned history table" support in SQL Server or Oracle for instance). I'll not go in depth on this, as I suspect simply letting the displayed name change when the user's details change is perfectly fine and easy to implement.

Side note: key type choice

With CHAR(36) I assume you are using UUIDs for internal user ID? Unless you have reason to use UUIDs (the requirement comes from elsewhere and is not in your control, you are writing a distributed system and are concerned about key collisions for users created in different locations, ...) you might want to consider using an automatically incrementing integer instead (INT NOT NULL AUTO_INCREMENT in mysql/mariadb, INT NOT NULL IDENTITY(1,1) in MSSQL, ...) which will only use 4 bytes per key.

The same for post IDs if you are using UUIDs for them also.

For space efficiency some DBS support packed UUID types (UNIQUEIDENTIFIER in MS SQL Server, UUID in postgres, ...) which will store the value in 16 bytes instead of 36, but I don't think mySQL does. You could use BINARY(16) but you may need to faf around converting back and forth when reading/writing the values so that probably isn't recommended.

Depending on the number of rows you expect the difference in key size may not be significant to worry about, but if you plan for your application to scale into something huge it should be considered in the early design stages (making key type changes is a nightmare once an application has already rolled out!).