By snazzybouche


2019-06-11 15:10:40 8 Comments

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?

1 comments

@BradC 2019-06-11 15:43:54

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.

@Jacob H 2019-06-11 16:04:31

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.

@snazzybouche 2019-06-11 16:13:12

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

@snazzybouche 2019-06-11 16:14:53

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

@BradC 2019-06-11 16:28:36

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

Related Questions

Sponsored Content

1 Answered Questions

2 Answered Questions

[SOLVED] How to create a table dynamically based on json data?

1 Answered Questions

4 Answered Questions

1 Answered Questions

[SOLVED] Create DDL Trigger Dynamically

1 Answered Questions

[SOLVED] Create a view that dynamically UNIONs new tables

2 Answered Questions

[SOLVED] Find changed columns for near-identical rows across tables

1 Answered Questions

3 Answered Questions

Sponsored Content