0

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!

2
  • In the past week, there was a similar question. The short answer is -- a single table. The longer answer was -- there are rare exceptions.
    – Rick James
    Nov 15, 2022 at 5:31
  • Even for the color table? The rest I could understand but I feel that the color table would be beneficial as separate. Please let me know!
    – Paul Ross
    Nov 15, 2022 at 5:47

2 Answers 2

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.

5
  • After reading your reply, does a separate color table make sense here? If the performance isn't improved that much, wouldn't I just be wasting space as I would have to store the columns userID and postID in the color table? Or would it help with the organization? 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?
    – Paul Ross
    Nov 15, 2022 at 21:29
  • @PaulRoss Please see my updated answer that addresses your additional questions.
    – J.D.
    Nov 15, 2022 at 22:44
  • I now understand the color table. However, I still want to confirm on the submitted table. If I have 1000 users who have 1000 unsubmitted posts each and who have 2 submitted posts each. Then the Blog table would have 1,002,000 rows ((1000 * 1000) + (1000 * 2)) and if I were to do 'select columns from posts WHERE userID = ? and submitted = 1', with indexing it would take log(1,002,000) = 6. Whereas if I had another table for Submitted Posts, the rows would be 2000 (1000 * 2), and if I do 'select columns from SubmittedPosts WHERE userID = ?' with indexing then it would take log(2000) = 3.
    – Paul Ross
    Nov 16, 2022 at 0:24
  • And since the difference is comparing log (1,002,000) = 6 to log(2000) = 3, which is small, having two different tables in this scenario shouldn't matter? Thanks for previous responses and please let me know!
    – Paul Ross
    Nov 16, 2022 at 0:26
  • @PaulRoss Yes, roughly right. You're using log10(n) (base 10) in your calculations. Index Seeks are probably not that good. I use log2(n) in my calculations, for the worst case scenario, because there's something called a fan out factor that generally makes it better than log2(n) actually. But none of that really matters since it's all a small number either way, and the point is relatively speaking the difference between log(1 million) vs log(2,000) (for any base value) is small, so small for modern computing that it's probably immeasurable to us developers.
    – J.D.
    Nov 16, 2022 at 0:47
0

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).

2
  • I now understand that since I can just use select columnName it makes little difference for multiple tables in the examples I gave above. But what if results were determined by WHERE completed = 1. 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 regardless of what columns you select?
    – Paul Ross
    Nov 15, 2022 at 21:28
  • It depends. It is likely that (in the long term) that only a /tiny/ fraction of posts will be unsubmitted. It /might/ be worth marking these separately (i.e. in another table), to make them easier to find. Having a "submitted or not" flag with only two values makes for a lousy discriminator and an index on such a field is unlikely to get used very often.
    – Phill W.
    Nov 16, 2022 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.