Use One Table or Many One-To-One Tables

Question

I am trying my best to learn SQL and I am sorry for the basic question, however, would it make sense to make this table Blog have numerous one-to-one table relationships? I feel that this table Blog has too many unnecessary columns and for most of the queries I do not need some of the columns, which leads me to think that this design is wasting performance.

The Table Blog
userID INT,
postID INT PRIMARY KEY,
title TEXT,
description TEXT,
createdTime DateTime,
submittedTime DateTime,
lastEditedTime DateTime,
titleColor TEXT,
customTitleColor TEXT,
showCustomTitleColor INT,
privatePost INT,
submitted INT

To explain further, the only time that I would ever need the columns titleColor, customTitleColor, and showCustomTitleColor is for two queries, clicking on a post and changing the color: every other query on table Blog would not need this. This makes me feel that putting these columns into a table Color with a one-to-one relationship with postID would be more efficient.

The Table Color
userID INT,
postID INT FK,
titleColor TEXT,
customTitleColor TEXT,
showCustomTitleColor INT

The Table Blog
userID INT,
postID INT PRIMARY KEY,
title TEXT,
description TEXT,
createdTime DateTime,
submittedTime DateTime,
lastEditedTime DateTime,
privatePost INT,
submitted INT

That way the query saves performance, however does this do more harm than good? Also the main query of Table Blog is querying titles based on the createdTime or submittedTime. This leads the columns lastEditedTime and description to not be used by most queries as they are only used when a post is clicked or their values get manually changed by an edit. Thus, is there a way to make table Blog more efficient when querying? Having two tables like this seem efficient but seems over-normalizing plus confusing.

The Table Blog
userID INT,
postID INT PRIMARY KEY,
title TEXT,
titleColor TEXT,
customTitleColor TEXT,
showCustomTitleColor INT,
privatePost INT,
submitted INT

The Table BlogTime
userID INT,
postID INT FK,
createdTime DateTime,
submittedTime DateTime

The Table ExtraInfo
userID INT,
postID INT FK,
description TEXT,
lastEditedTime DateTime

Is there a better method or should I just use table Blog? And for my final question, just to confirm, having a one-to-one table like below is pointless even if you can "group" the data correct?

The Table Blog
userID INT,
postID INT PRIMARY KEY,
title TEXT,
description TEXT,
titleColor TEXT,
customTitleColor TEXT,
showCustomTitleColor INT,
privatePost INT,
submitted INT

The Table BlogTime
userID INT,
postID INT FK,
createdTime DateTime,
submittedTime DateTime,
lastEditedTime DateTime

Please let me know and thank you so much for your time!

Answer 1

It depends, and generally won't matter much one way or the other as far as performance is concerned.

The reason why it won't improve performance much to split up the table into multiple tables, is because your queries should be SELECTing only the fields they need already, not all fields, and never SELECT * which is an anti-pattern.

It can improve concurrency performance though, if that was a bottleneck. For example, if someone needed to update the Blog table at the same time that someone else needed to update the Color table, for the same postID, then both can happen concurrently without blocking each other. Another use case is if you need to make changes to the schema of one of the tables, it won't lock the other table, again allowing better concurrency.

The rule I typically follow is I design my tables to match the objects that they will concretely represent in the consuming application. If in the application, the idea of a separate Color object makes sense, then I'd probably create a separate table for it. Other times, I'll just create the core object, e.g. Blog, and have one additional meta-data table called BlogExtended, for the more loosely coupled attributes of the object.

---

To your follow up questions in the comments:

wouldn't I just be wasting space as I would have to store the columns userID and postID in the color table?

An INT is only 4 bytes, so we're talking 8 bytes per row of extra space consumed (before compression). In a 1 million row table, that's only 8 MB of total extra disk space. My graphing calculator would be ok with storing that much extra data, any modern database and storage system should be just fine too.

Or would it help with the organization?

That just depends on your use cases and if you think it makes sense to have a separate Color object in the consuming application. Again, we're splitting hairs right now based on the information you've provided, so it probably won't matter one way or the other.

Also let's say that users have a lot of unfinished posts but few completed posts. Ex. the user has (100) unfinished posts but only has 2 submitted posts. If all the main queries are going to be done on submitted posts, wouldn't it make sense to just have a separate table for submitted posts as the queries would be faster?

Nope, because that's a linear, aka O(n) search time, design change. Proper indexing on a table uses B-Tree data structures that are O(log(n)) search time, which is exponentially faster. If the table had 1 billion rows in it, in the worst case O(log2(1 billion)) = 30. That means in the worst case, to search through a table with 1 billion rows in it, only 30 nodes of the B-Tree would actually have to be seeked through to find the data you're looking for. If the table grew to 1 trillion rows, it would only be 40 nodes that need to be seeked through. These are extremely tiny numbers of objects that any modern computer can handle in milliseconds.

Separate tables just help with concurrency blocking problems, sometimes, as I mentioned earlier in my answer. But you won't see any tangible improvement on actual unblocked execution times of queries, compared to a properly indexed table.

Answer 2

Question: What are you going to do most often with a post?
Answer: Load it and display it.

For that, you will need most, if not all, of those fields, including the colours, even if only once.

As others have said, select * is a seriously Bad Idea.
You never know when someone [else] will add a dozen, new BLOB fields to "your" table, with gigabytes of video data in them. If you only needed, say, the postID, that would be a lot of unnecessary network traffic, slowing things down.

Obviously, though, monitor your running Application and if pulling lots of fields in one go does seem to be causing a performance problem (it probably won't), then worry about optimising things (although vertical partitioning like this rarely makes much, if any, positive difference).