While I, personally, prefer the RDBMS approach myself, and continouusly find new amazing ways of doing things when SQL is your only tool, a forum could be implemented as a flat file system, with top posts being directories and replies being files in that directory (and the top post content being a special file in the post directory). That way, the structure is immediately visible and can easily be manipulated through the standard OS tools, but searching becomes unwieldly.

It also has the advantage of being a low-tech approach, where you don't need a database server. Concurrency becomes a problem, but as long as you prohibit editing of posts, you can avoid that problem.

To get back to your particular example, I've implemented discussion boards (in perl) using three different back-ends. The first used NNTP and used inn as the "database" to store the discussion. The second used DB_File (one file per "discussion"), and the third used a SQL database to store the message meta-data (though not the message text - that was stored separately in the file system).

All in all the third technique gives you the most power, and the most flexibility, and automatically handles things like concurrency, update locking, etc.

If I had to do it again I'd definitely store this sort of data in a SQL database, unless it was shown that this solution wouldn't provide sufficient performance (though I'm not sure what other solution might provide better performance.)

Michael

One technique that I see frequently splits the message body from the header info. The message body is stored as a flat file in a directory, but the header and threading information is stored in an RDBMS.

Where this really comes into its own is when you store the headers in an RDBMS on a SAN, and keep the file refs for the bodies on a NAS system. It scales wonderfully huge!

If the data that would be stored in the database has few relationships, then you wouldn't necessarily have to use a fullblown RDBMS; you could simply use a collection of flat files, or a simpler DB, like .dbm files. That way, you could exploit things like tie; Tie::File for files, and even DBI for more complex structures.

I always wanted to code a forum that stores the bodies in a table like

PartID, MessageID, ThreadID, BodyPart VARCHAR(254)

so one could select a message with:

SELECT BodyPart FROM Body WHERE MessageId=? ORDER BY PartID

and all messages of a thread with:

SELECT MessageID, BodyPart FROM Body WHERE ThreadId=? ORDER BY MessageID, PartID

and get a preview of a message with:

SELECT BodyPart FROM Body WHERE MessageId=? where PartID=0

Fulltext search with preview:

SELECT MessageID, ThreadID, BodyPart FROM Body WHERE BodyPart LIKE '%foo%' ORDER BY MessageID

or, or, or... :-)

---

_Search, Ask, ^Know

That's all good (well, modulo a minor normalization quibble), except the full text search:

select ... from ... where BodyPart like '%foo%' ...
[download]

On a small dataset this will be OK, but it won't scale, as it requires a table scan (or at the very least a full index scan if BodyPart is indexed) for databases that use B-Tree (and derived) indexes due to the leading wild-card (%).

Michael

For small things I like DBD::SQLite, and I expect that one of the advantages will turn out to be easy 'upgrading' to a stronger DBMS (I have just started using SQLite). Apart from heavyweights Oracle and Sybase, there is also a MySQL choice of different underlying engines: InnoDB for heavier work, MyISAM (and other types) for lighter and faster use.

DBMS will always have the advantage of scalability, and if a database (or enough index) fits into memory, performance will be all right anyway.

Maybe I suffer from the same affliction, but I would say RDBMS is a very versatile hammer to cast problems into nails with.

IMHO there is absolutely nothing wrong with thinking of data in relational terms. After all, there is a clear isomorphism between a row which has columns, and an object which has data members. The one fundamental difference — which could be seen as a shortcoming of the relational paradigm — is that objects have behavior. And the definition of behavior is what really constitutes "type". Of course, the OO paradigm has its own difficulties. How do you join two vectors of objects? Or, How do you implement an object which, conceptually, is relationally multiple tables?

I know, some will say that these are solved problems. And that some systems, e.g. Oracle's PL/SQL, give RDBMS the OO that it needs. But these are extensions; they're non-fundamental.

In a threaded fourm, the major difficulty you'll probably find is in creating the tree structure required for output. This isn't a limitation of RDBMS, but of SQL. It's possible to build a tree out of SQL queries, but it gets ugly fast.