I am in the middle of creating my first blog, and I am at the stage where I now need to develop my database. It will be a MS SQL 2005 database and I need to meet the following:
Select last 5 posts entires for the homepage
Select * posts where category = ‘@categoryid’
select post, comments where postid = ‘@postid’
These one’s are more layout based:
On my homepage I would like to have a list of my Category names and within a pair of brackets display how many posts are related to that category.
Also I would like to have the same ability to list the last 6 months (and display how many posts were made within that month.
So far I have the following for my database, but can I get your input into changes and ways to query and get my desired results (as above?)?
In case your blog becomes popular this could be a pain to filter posts in-between lots of comments. And with that replies to comments approach calculating comments for the post is also a little more complex task
I meant pain for DB server, not for coder Imagine that you have 5000 posts with 100 comments per post. Not a great pain but still
Well, this is another field you haven’t mentioned. Anyway I’ve used a similar approach in one application (this was with MS Access something like 8 years ago) and it turned into constant performance optimization nightmare. That wasn’t a blog however…
Not really. If you have the right indexes on the table, 5,000,000 rows should not really hurt a thing on proper hardware. Databases are designed to look up things. Fast.
Well, this is another field you haven’t mentioned. Anyway I’ve used a similar approach in one application (this was with MS Access something like 8 years ago) and it turned into constant performance optimization nightmare. That wasn’t a blog however…
I am trying to learn from this whole project and I also want the experiance of developing a database that properly interacts with my .net code. So ideally I would like to develop it myself. I had a look online but couldn’t find any real information/diagrams to do with the structure of the SubText database, do you know of any?
So can you please post your structure then please? I found an old post where you demonstrated your single table approach and it makes sense to me but how would I go about dealing with the categories? Would they be in a seperate table or would they be better located within the single table?