As a learning exercise I'm trying to put myself a blogging system.
The goal is to code something that will let me create multiple blogs, like blogger.com or wordpress.com, but much simplified.
I would like to ask you, what do you think is best database design for this type of script.
Is it better to have one big table, containing posts from all blogs of all users (like friendfeed) or would it be better to create separate table for each blog's posts?
Big thanks in advance for your help, Peter.
Your most efficient approach is going to be to create a properly normalized database (authors, posts, comments tables) and then as you scale look for ways to deal with your performance problems when they come up rather than design for problems up front... those problems may never materialize.
Typically, if you are going to split a single table into multiples you need to look at how the data is being queried and determine the best way to partition the data. It may be that time is a better way to partition it or that you want to partition by author or even by blog url.
In summation - start with a traditional normalized design and only stray from that path when you run into performance problems.
This is a good question (+1).
In terms of scalability it is obvious that one table for every blog from every person is not going to fly unless you have a very big budget. Maybe FriendFeed can afford to push a server farm at a table - but I would be interested to read your research on them.
If you are only expecting a small number of users then concentrate on the design and ignore the scalability.
I guess the answer depends on how far you want to scale your project.