Large strings: Text files or SQL DB?

Question

I am coding a forum system using PHP.

I am currently storing a threads ID, title, author, views and other attributes in an SQL database and then storing the thread body (the HTML and BBcode) in text files inside a folder named after the thread ID.

In practise it's really simple to grab the database values then just grab the thread body from the text file, but I was wondering if this is the 'proper way'? I have personally no problems doing this but if it turns out it is massively inefficient and I should instead store both the thread body HTML and BBcode in the database instead then I will change.

However, to me it seems wrong to store such a (very possibly) huge string of multi-line text along with lots of different characters in a database - I was taught that databases are more for short field 'values' rather than website content.

I would just like a definitive answer to this because it's been bugging me for ages as to wherever I’ve been doing it properly.

Does anyone know how popular forum systems store threads?

Added Thanks for the answers, so it's best to store thread content in the database, what field type should i use? Also what about replies? Another table which has the thread ID and comment ID then the comment body? I'm new to this database stuff so thanks for your help.

Answer 1

It would be best practice to store the thread in the database since that will allow you to scale and search easier.

If you want to continue using files to store the content, I would recommend using something along the lines of GridFS. Basically just chunks up files and stores them in NOSQL.

Answer 2

Confluence (a commercial wiki) stores the entire page content within a single column.

The reason to store large text in the database is:

1. There's (hopefully) no disconnect between the value and the record(s) the text is associated with
2. There are technologies like Full Text Search (FTS) to make finding specific strings in large amounts of text
3. Simplified backup & restore process

Answer 3

I know that DotNetNuke and the AspDotNetStorefront use a database to store such data. These aren't forums, but a content management system and a shopping cart with content management capabilities.

I've also experimented with several forums (Such as YAF) and all of those use databases as well. Personally, I'd stick with a DB for the HTML, and any image/content files should be stored on the disk with a reference to their location in HTML.

Perhaps the strongest argument for storing in the DB: It's a heck of a lot easier to search the text fields with a LIKE clause than to search for a strong in a text file.

Also, with free forum software cout there, can I ask why you're writing a new one from scratch? I realize there are probably good reasons, but just in case it's something you hadn't thought of yet...

Added

Most of my references were .NET code. Here's an open source forum written in PHP: http://www.phorum.org/

Answer 4

This is an aside as the question has already been accepted, however you should check out phpbb3 (http://www.phpbb.com/). Very robust php forum. May save you some development time :D

Answer 5

I agree with the other answers, storing all the data in your database simplify scaling, backup/restore, allows you to query the data and so on.

If you're concerned with performance, you could implement a cache for the page content. I know PHPBB does this by having a serialized array in a text file with a expiration timestamp. Could also be done using memcached or otherwise.

Storing the data in a database give you the most flexibility and convenience, most problems related to serving the data to the end-user can be handled by caching the data.