Thread: typical schema for a forum?

Hi,

I'm trying to implement a forum with mason and postgresql. What is the
typical database schema of a forum (threaded or flat) application?

Thanks,

    I have written a little PHP+postgres forum for benchmarking purposes, to
see how fast postgres could go.
    It has basic forum features, like forums (duh), topics, posting,
pagination, watching topics, topic & post count, display newest topic and
post in topic & forum pages, templates, topics pagination in O(1) not
O(number of pages), etc.
    I also wrote a companion benchmark script which tortures the server with
HTTP requests, viewing and posting.

    it answered my "how fast can it go" question : more than 500 dynamic
pages/second on a Core 2. Most of the load is PHP : postgres could handle
much, much more than this.

    If people are interested in this as a benchmark, I can write some docs
(and make a MySQL version).
    If you want to use it as a base for your forum, you're welcome.

    Who wants the source ?

On Wed, 09 May 2007 10:49:17 +0200, Louis-David Mitterrand
<vindex+lists-pgsql-general@apartia.org> wrote:

> Hi,
>
> I'm trying to implement a forum with mason and postgresql. What is the
> typical database schema of a forum (threaded or flat) application?
>
> Thanks,
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

On Wednesday 09 May 2007 04:49, Louis-David Mitterrand wrote:

> I'm trying to implement a forum with mason and postgresql. What is the
> typical database schema of a forum (threaded or flat) application?

Try downloading and installing a few free forum packages:

  phpbb
  phorum
  bugzilla
  redmine

Some of these may work better with MySQL than with postgresql;  but you don't
need to copy one of their schemas exactly.  They all have a lot of cruft.

I'd expect to see a few entities in any sort of database-driven forum:

  post (subject, date, text)
  user (name, email, password) - unless using e.g. an external LDAP directory
  relationship "user makes post"
  relationship "post responds to other post"
  relationship "same thread as" (this is actually the transitive closure of
the union of "post responds to other post" and its inverse,  but is generally
implemented with a "thread ID" field for efficiency)

There is also the alternate "same thread as" relationship,
post1.subject=post2.subject.

The e-mail and newsgroup RFCs would also be a good source for architectural
ideas.

--
David Lee Lambert (recent WSU grad) <as4109@wayne.edu>
Cell phone:  +1 586-873-8813
Work email:  dlambert@bmtcarhaul.com