What would be the best way of designing a database to store blog posts and comments? I am currently thinking one table for posts, and another for comments, each with a post ID.
It seems to me, however, trawling through a large table of comments to find those for the relevant post would be expensive, and would be done every time a blog post is loaded (perhaps with some amount of caching).
Is there a better way?
Every database system you would be using to implement your blog will use indexing. What this means is that, rather than "trawling through a large table", your database system maintains a seperate list of comments and which posts they are associated with, much like the index at the back of a book. This allows the database system to load comments associated with a post extremely quickly, and I don't see any problems with your proposed design for a blog of any size.
Indexes are routinely used to associate tables with millions of rows with other tables with millions of rows - you would have to have an exceptionally large blog to require denormalization of comments, and even still, caching would probably serve you far better than denormalizing the database.
You will need to define an index on your comments table, and associate it with whatever column holds the Post ID. How that's done is dependent on what database system you are using.
trawling through a large table of comments to find those for the relevant post would be expensive,
An index is always there to rescue you! First index on postId and another of commentdate (desc)
It seems to me, however, trawling through a large table of comments
All the database vendors agree with you.
They offer "indexes" to limit this.
try something like this:
Blog
BlogID int auto number PK
BlogName string
...
BlogPost
BlogPostID int auto number PK
BlogID int FK to Blog.BlogID, index
BlogContent string
....
Comment
CommentID int auto number PK
BlogPostID int FK to BlogPost.BlogPostID, index
ReplyToCommentID int FK to Comment.CommentID <<for comments on comments
...