Re: typical schema for a forum?

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(at)wayne(dot)edu>
Cell phone: +1 586-873-8813
Work email: dlambert(at)bmtcarhaul(dot)com