Let's say that I'm making an IRC bot, and in addition to all the other stuff that should be stored in the DB, I want to log all the messages in each channel in which the bot is present.

I already have a channels table with columns id, channel_name, and some other info.

For logging each message, what seems the most sense to me is to create a messages_channelname table for each channel, and in there store the message along with whatever other information is associated with it.

That elicits a problem, though - the name of the table is now dissociated from the channel's id (although I could alleviate this by making the table name messages_channelid).

I feel like the best practice here, rather than what makes the most sense, would be to have a master messages table that contains the messages from all channels, with columns channel_id, id and message, where id would be the index of the message per channel. But then I'd have (unfounded) concerns about table size and possibly search speed.

What's the best way to go about this?


There isn't any definitive answer for this problem.

You've correctly identified some of the possible tradeoffs (single table=simpler/possibly slower/more contention?, multiple tables=more complicated/faster, maybe?/harder to use in other contexts?).

The general advice is to start with the more straight-forward single-table solution, then break them out if testing shows problems.

Most of the time you're going to get good performance on a single table as long as you have decent hardware and proper indexing design.

Depending on the specific platform, you might have other technical solutions like filtered indexed views and stuff like that.

  • 1
    Other things to consider, like what are the message contents? Text in NVARCHAR(100) columns? Binary data? What will you be querying for? I have seen systems have a "messages" table and an "attachments" table to keep the most queried tables very narrow and tightly indexed. Then only when a user "opens an attachment" the fatter table is queried. This also depends on how the application is being developed and used. I definitely would avoid dynamically creating tables unless you are seeing performance issues.
    – Jacob H
    Jun 11 '19 at 16:04
  • @JacobH That's a good point. In my case my data is simple, I only need to store sender nick, the timestamp and the message as a string up to 256 chars iirc. Maybe a single table would be fine. Jun 11 '19 at 16:13
  • @BradC "as long as you have decent hardware" - this is absolutely not the case here, as this'll be running on a low-end rpi3. But "decent" is subjective... A test-oriented approach seems like good advice. Jun 11 '19 at 16:14
  • 1
    @snazzybouche Yeah, for something that simple, definitely start with a single table with the appropriate indexes on your channel_id. Even on modest hardware, modern rdbms's can probably do that without breaking a sweat until you start getting up into the very high transactions-per-second range.
    – BradC
    Jun 11 '19 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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