blog database design

[Muller2]

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:

4) 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.

5) 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

[ailon]

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

[wwb_99]

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

[r937]

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

[ailon]

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

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

[r937]

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

where in_reply_to is null

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

group by thread_starter

[ailon]

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

where in_reply_to is null

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

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

group by thread_starter

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

[wwb_99]

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

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

[r937]

Well, this is another field you haven't mentioned.

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

[Muller2]

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

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?

[Muller2]

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

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

[r937]

... but how would I go about dealing with the categories?

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
;

[Muller2]

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

[r937]

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

[Muller2]

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

[wwb_99]

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

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?

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

[Muller2]

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

[r937]

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?

sure, let's make one up right here

suppose you have these two tables --

 A     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

[r937]

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

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

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

[Muller2]

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

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

Please don't applogise! Your help is ace!

Al

[longneck]

sure, let's make one up right here

suppose you have these two tables --

 A     B
102   101
104   102
106   104
107   106

here are what you'd get for the various joins --

best... post... ever...

[r937]

thanks, longneck

[Muller2]

Rudy,

I have tried creating this table in Ms2k5 but I get an error simular to before

CREATE TABLE t_blog_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
, 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
;

Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'AUTO_INCREMENT'.


Is this a syntax error becasue it was written for mysql? Can you highlight what I need to change so I can get the table to create in ms2k5 please?

Thanks,

Al

[r937]

Is this a syntax error becasue it was written for mysql?

yes

CREATE TABLE (Transact-SQL)

[Muller2]

cool I had managed to find some stuff on google and changed that error but the next error appears:

CREATE TABLE t_blog_posts
( id INTEGER NOT NULL Primary Key IDENTITY(1,1) 
, 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
, 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
;

Error now:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'titles_ux'.

I don't know what to google to begin fixing this error though? Can you advise me please?