PostgreSQL & PHP Tutorials - Database Design Issues - New Database Fields

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

Posted By Chris Smith Posted on 20 Apr 2006, 10:54 PM
Viewing page 3 of 5
« | Back | Next | »

Instead of running that big, complicated query I decided to use some more advanced features of postgresql and handle things a little differently.

I added two new columns to the forums table:

ALTER TABLE myforum_forums ADD COLUMN postcount INT;
ALTER TABLE myforum_forums ALTER postcount SET DEFAULT 0;

ALTER TABLE myforum_forums ADD COLUMN threadcount INT;
ALTER TABLE myforum_forums ALTER threadcount SET DEFAULT 0;

then update existing rows:

UPDATE myforum_forums SET postcount=0, threadcount=0;


Then we do the same for threads:

ALTER TABLE myforum_threads ADD COLUMN postcount INT;
ALTER TABLE myforum_threads ALTER postcount SET DEFAULT 0;

UPDATE myforum_threads SET postcount=0;


Postgresql currently doesn't let you add a field to a table and set a default at the same time, but this is easy to overcome as you can see by running the two steps separately. This functionality may become available in future versions, but since it can easily be done in two stages, there won't be a huge need for it. See alter documentation for more info.
Viewing page 3 of 5
« | Back | Next | »



Avg Rating: 5
Vote Count: 6


              
  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 24 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  : 4 + 5 =
Comment