I´m creating a forum engine, and I have 3 tables:

CREATE TABLE forum_areas (
ID VARCHAR2(32) NOT NULL UNIQUE,
name VARCHAR2(150) NOT NULL UNIQUE,
description VARCHAR2(1024) NOT NULL UNIQUE
);

CREATE TABLE forums (
ID VARCHAR2(32) NOT NULL UNIQUE,
areaID VARCHAR2(32) NOT NULL,
name VARCHAR2(150) NOT NULL,
description VARCHAR2(1024),
forumscript CHAR(1) NOT NULL,
emoticons CHAR(1) NOT NULL,
class CHAR(1) NOT NULL,
creationdate DATE
);

CREATE TABLE forum_posts (
ID VARCHAR2(32) NOT NULL UNIQUE,
forumID VARCHAR2(32) NOT NULL,
parentID VARCHAR2(32),
type CHAR(1) NOT NULL,
title VARCHAR2(128),
text CLOB NOT NULL,
author VARCHAR2(32),
iconID VARCHAR2(32),
forumscript CHAR(1) NOT NULL,
postdate DATE,
ip VARCHAR2(32),
views NUMBER DEFAULT '0' NOT NULL,
rating NUMBER,
numrates NUMBER DEFAULT '0' NOT NULL
);

Table forums stores the main forum info, such as the name, description, etc. forum_areas stores the area name (assigned to the forum by forums.areaID=forum_areas.ID). forum_post stores the messages (topics and replys).

My question is... if I want to show the list of available forums (including number of posts in forum), which method is better?

- adding columns "number of topics" and "number of replys" to table "forums" to store the number of messages. (creating a trigger to update that info each time a new message is posted).

- Catch all the info (Only one SELECT with nested SELECT count(*) each time the forum list is shown) --> Not sure about process time and required cpu resources

- Create a new view with count(*) information in columns "number of topics" and "number of replys" --> Not sure if there is *real* difference between this choice or the previous one.

Thanks!