PostgreSQL & PHP Tutorials - Database Design Issues

PostgreSQL »  Database Design Issues
Random Stuff »  Database Design Issues
Forum Project »  Database Design Issues

Posted By Chris Smith Posted on 20 Apr 2006, 08:54 AM
Viewing page 1 of 5
« | Back | Next | »

Database design is a difficult topic. A lot of it depends on the application you are writing and how you want to access the data.

Whilst writing my forum, I decided to do things slightly differently to a normalized setup to speed things up.

There are lots of tutorials available on database normalization so I won't go over it again, instead I'll jump in and show how postgresql's features can keep things nice and speedy whilst not losing any functionality or creating any data integrity headaches.


The forum will show some basic stats when you view a particular area:

- the number of threads in that forum
- the number of posts in that forum


The (simplified) relevant forum database tables look like this:

CREATE TABLE myforum_forums (
    forumid int not null default 0 primary key,
    forumnode varchar(200),
    forumtitle varchar(255),
    forumdescription text,
    createdate timestamp DEFAULT NOW()
);


CREATE TABLE myforum_threads (
    threadid int not null default 0 primary key,
    forumid int REFERENCES myforum_forums(forumid) ON DELETE CASCADE,
    threadnode varchar(200),
    threadsubject varchar(255),
    threadauthor int REFERENCES myforum_users(userid),
    createdate timestamp DEFAULT NOW()
);


CREATE TABLE myforum_posts (
    postid int not null default 0 primary key,
    postnode varchar(200),
    threadid int REFERENCES myforum_threads(threadid) on delete cascade,
    postsubject varchar(255),
    postcontents text,
    postauthor int REFERENCES myforum_users(userid),
    posttime timestamp DEFAULT NOW()
);
Viewing page 1 of 5
« | Back | Next | »



Avg Rating: 3
Vote Count: 20


              

  New Reply


You've missed an important point. Consider the following:

---
create table forums (
forumid int primary key
);

create table threads (
threadid int primary key,
forumid int references forums
);

create table posts (
postid int primary key,
threadid int
);

insert into forums values(1);
insert into forums values(2);

insert into threads values(1, 1);
insert into threads values(2, 1);

insert into posts values(1, 1);
insert into posts values(2, 1);

insert into posts values(3, 2);
insert into posts values(4, 2);
insert into posts values(5, 2);
insert into posts values(6, 2);

select f.forumid, count(distinct t.threadid), count(distinct p.postid) from forums f
left outer join threads t on(f.forumid = t.forumid) left outer join
posts p on(t.threadid = p.threadid) group by 1;

---

This is a correct design and simpler query. However, this is not the best way to do what you're trying to achieve in PostgreSQL. Instead, you should use rules and triggers to maintain a forum, thread and post count in a count cache table. This will give you O(n) count performance and can be done without modifying your application code. Just create insert and delete triggers/rules to manage the counts when posts/threads are added and removed.
Gavin Sherry 23 Apr 2006 Reply
`-- I thought that's what I was trying to demonstrate.. or do you mean having a separate count table instead of including it in the forum/thread table ? Chris Smith 24 Apr 2006 Reply


Want to post a comment? Fill in the details below.

Your Name  : 
Your Email  : 
Your Website  : 
Spam Check! Please answer this question  : 5 + 5 =
Comment