Blog database design

Databases
1

All,

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:

  1. Select last 5 posts entires for the homepage
  2. Select * posts where category = ‘@categoryid’
  3. select post, comments where postid = ‘@postid’

These one’s are more layout based:

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

  2. 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?)?

postid <<pk>>
posttitle
postbody
posttimestamp

postid
commentsid
(Composite foreign key?)

commentsid <<pk>>
commentsbody
commentsemail
commentsurl
commentsname

categoryid <<pk>>
categoryname

postid
categoryid
(Composite foreign key?)

Many thanks for your time.

Al

2

Why would you want to link comments to post through an intermediate table? Do you plan to have one comment for several posts? :wink:

3

I would go and look at a functioning blog application, such as SubText, to see a working blog database model.

In fact, at this point I would not build a blogging package from scratch as anything other than an academic exercise . . .

4

in my blog design i put the post and the comment into the same table, because they have so many common columns

the table uses a column called “in_reply_to” and when this is NULL, the row represents a post, and if it isn’t, then it’s a reply/comment

notice that this nicely allows you to have comments on other comments, and not all just tied to the main post

5

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

6

a pain to filter posts in-between lots of comments? no :slight_smile:

where in_reply_to is null

calculating comments for the post is also a little more complex? no :slight_smile:

group by thread_starter
7

I meant pain for DB server, not for coder :wink: Imagine that you have 5000 posts with 100 comments per post. Not a great pain but still :wink:

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…

8

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…

Access is a performance nightmare . . .

9

of course – because it was in response to a requirement that you haven’t mentioned until just now

:slight_smile:

10

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?

11

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?

This is the old post: here

Thanks,

Al

12

they would be in a separate table, and there would be a many-to-many relationship table to relate posts to categories

CREATE TABLE posts
( id   INTEGER   NOT NULL PRIMARY KEY AUTO_INCREMENT
, in_reply_to INTEGER      NULL
, title       VARCHAR(255) NOT NULL
, descr       VARCHAR(255) NULL
, url         VARCHAR(255) NULL
, added       DATETIME     NOT NULL
, chged       DATETIME     NOT NULL
, other_columns ...
, UNIQUE titles_ux ( title )
, KEY in_reply_to_ix ( in_reply_to )
, FOREIGN KEY in_reply_to_fk ( in_reply_to )
       REFERENCES posts ( id )
) TYPE=InnoDB
;
13

so the category table would be


CREATE TABLE posts
( catid   INTEGER   NOT NULL PRIMARY KEY AUTO_INCREMENT
, subcatid INTEGER      NULL
, cattitle       VARCHAR(255) NOT NULL

That would give me catid, subcatid, and cattitle.

Then would I have a table called relationships that had


catid,
postid

(As Composite key’s?)

Thanks Rudy,

Al

14

the relationship table is correct – two columns, each a foreign key to its respective table, and the pair together as a composite primary key

as for the category table, i would not use subcatid, rather parent_id

perhaps this may help – Categories and Subcategories

15

cool thanks rudy - I don’t need subcategories yet so I might look into that for a future task!

Also the biggest issue I have with sql is knowing which join to use, is there a ‘grid’/diagram that highlights which one is for which task?

Thanks again.

Al

16

The best source is probably the source code itself. Get it from the project’s sourceforge site.

17

Rudy,

I get the following error in ms sql 2k5 when running this to create the tables


create table categories
( id       integer     not null  primary key
, name     varchar(37) not null
, parentid integer     null
, foreign key parentid_fk (parentid)
      references categories (id)
);

The error is:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ‘parentid_fk’.

Is that because there is _fk on the end?

Thanks

18

sure, let’s make one up right here :slight_smile:

suppose you have these two tables –

[B] A     B[/B]
102   101
104   102
106   104
107   106

here are what you’d get for the various joins –

A inner join B
102   102
104   104
106   106

A left outer join B
102   102
104   104
106   106
107   null

A right outer join B
null  101
102   102
104   104
106   106

A full outer join B
null  101
102   102
104   104
106   106
107   null

A cross join B
102   101
104   101
106   101
107   101
102   102
104   102
106   102
107   102
102   104
104   104
106   104
107   104
102   106
104   106
106   106
107   106
19

my apologies, that article was using mysql syntax, the correct syntax is


, constraint parentid_fk foreign key (parentid)
      references categories (id)
20

Please don’t applogise! Your help is ace!

Al