GC-BBoard Module Design

part of the ArsDigita Community System by Tzu-Mainn Chen

I. Essentials

II. Introduction

At arsDigita, one of the more frequent requests is for a threaded bulletin board. ACS 4.0 will contain an updated bboard that will meet current requirements; however, it has not yet been released, and many clients want a threaded bboard now.

When this issue came up for USLaw, Michael Yoon proposed a quick-and-dirty solution: implement bboard functionality using the general-comments module (hence gc-bboard), thus making development simpler and more supportable by pre-existing ACS code.

III. Historical Considerations

. . .

IV. Competitive Analysis

. . .

V. Design Tradeoffs

. . .

VI. Data Model Discussion

The data model is not extensive, as the major chunk is pre-written in general comments. The one addition to general_comments is a boolean intended to mark a comment as part of a bboard or not. This allows for easier querying. 

alter table general_comments add (bboard_p char(1) default 'f' check(bboard_p in ('t','f')));

create index gc_comment_date_idx on general_comments(comment_date);
create index gc_show_p_idx on general_comments(bboard_p, approved_p);
create index gc_comment_on_what_ids_idx on general_comments(comment_id, on_what_id);

create or replace view gc_bboard_messages
as select * from general_comments where bboard_p = 't';
The major new table is gc_bboard_topics: 

-- NEW TABLES

create sequence gc_bboard_topics_id_seq;

create table gc_bboard_topics (
	topic_id   	integer primary key,
	name		varchar(100) unique not null,
	active_p 	char(1) default 'f' check(active_p in ('t','f')),
	max_bboard_display_depth integer default 2,
	max_bboard_display_messages integer default 10,
	creation_user	references users,
	creation_date   date default sysdate,
	creation_ip	varchar(50),
	group_id	references user_groups,
	auto_subscribe_p char(1) default 'f' check (auto_subscribe_p in ('t', 'f'))
);

-- denormalization to make the topic of each comment easily accessible
create table gc_comment_topic_map (
	comment_id	references general_comments,
	topic_id	references gc_bboard_topics,
	primary key(comment_id, topic_id)
);
The few remaining tables are for alerts: 


-- when a bboard entry is inserted, an insertion is done into this table as
-- well, so we know if a bboard entry needs to be processed for alerts
create sequence gc_bboard_alert_id_seq;
create table gc_bboard_alerts (
	alert_id	integer 
			constraint gc_bboard_alert_id_pk
			primary key,
	--didn't specify it to be unique since message might be edited
	comment_id	references general_comments not null,
	--if date_sent is null, that means not sent yet
	--check to see if approve before sending
	date_sent	date
);

--  shows who should get an email for that message; entries are deleted once
-- an email is sent
create table gc_comment_user_recipients (
	comment_id	references general_comments,
	user_id		references users
);

--gc_topic_user_map shows who is subscribed to a topic
create table gc_topic_user_map (
	topic_id	references gc_bboard_topics,
	user_id		references users,
	primary key (topic_id, user_id)
);

--gc_comment_user_map shows who is subscribed to a thread
create table gc_comment_user_map (
	comment_id	references general_comments,
	user_id		references users,
	primary key (comment_id, user_id)
);

--gc_contributor_user_map shows who is subscribed to an author
create table gc_contributor_user_map (
	contributor_id  references users,
	user_id		references users,
	primary key (contributor_id, user_id)
);

--gc_keyword_user_map shows who is subscribe to a keyword
create table gc_keyword_user_map (
	keyword         varchar(100) not null,
	user_id 	references users,
	primary key (keyword, user_id)
);

VII. Legal Transactions

Site Administrators

Site administrators may perform group administrator functions for every bulletin board topic.

Group Administrators

Group administrators can create public or private group bulletin boards. They can also edit messages, toggle message approval, delete message branches, manage various display and alert properties, and administer bulletin board permissions using general_permissions.

Users

Once they are registered, users can post messages in public bulletin boards, or in any group bulletin board of which they are members. They can also sign up for various alerts, based on topic, author, thread, or keyword.

VIII. API

There are two essential procedures : There are a few minor procedures and scheduled procs:

IX. User Interface

. . .

X. Configuration/Parameters

None, really, other than those described in Section VIII.

XI. Acceptance Tests

. . .

XII. Future Changes

tzumainn@arsdigita.com