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()
);