CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Jake Chasan
Jake Chasan

Reputation: 6560

Structure Database for Message Board

If I were to create a SQL database for a message board, is the following configuration recommended to store the replies to a topic?

Table - Topics

Table - Replies

The reason that I ask is because it would seem to me that after hundreds of thousands of rows in the replies table, loading a topic would take a long time, as the server would have to search for all mentions of the Topic ID in the replies table, then assemble them and present them to the user.

Any thoughts on how to structure this?

Upvotes: 1

Views: 497

Answers (1)

Kritner
Kritner

Reputation: 13765

This is a pretty basic use case of indexes.

In your scenario, you would likely throw an index on the topic Id column (and potentially include date). When doing lookups on your replies table, at least in a message board browsing scenario, you would likely have a single topic to get replies for. All your queries will be something to the effect of:

select *
from replies
where tepicId = 1 -- my specific topic id example

the index (depending on clustered or non-clustered) either orders the data in the order of the index (clustered), or the pointer location to the data on the clustered index (non-clustered) - this allows for much faster retrieval of data in a appropriately targeted query (like asking for a specific topicId when having an index on topicId).

The problem with indexes, though they (generally) can make querying faster (assuming appropriate indexes), the more indexes you have, the longer your insert statements are going to take. This is due to the data being written to multiple places - the table itself, and the indexes supporting the table.

You can read more on the descriptions of clustered vs nonclustered indexes here: What do Clustered and Non clustered index actually mean? it explains it much better than I ever could :)

And for your specific question, utilizing an index described above, you really shouldn't see any issues with data retrieval as far as row numbers are concerned - if anything it could be the amount of data that needs to travel across the line. In an extreme scenario if each "reply" was 1MB in length and there were 100 replies to a thread, you would need to worry about the data transfer time due to a transfer of 100MB, but the retrieval from the DB should be good and quick.

Upvotes: 1

Related Questions