blob vs text in forum

Question

I'm writing a forum website, but i am having a problem with designing the database to hold the posts right now. From what I have read, phpBB 3 stores the user posted data as BLOB, and some store data as text. Is there an advantage that one would have over another? What about max characters if one were to store the data as text? I have never retrieved BLOB data from database and parse it to text (or whatever data it is meant to be for that matter) but I think learning how to use it would be interesting.

The text data for the post will be utf8 encoded. I'm using MySQL database.

Also, how would you allow uploading images for the forum and should the images be stored as part of the database, like in a BLOB for example, should should it be stored as individual files?

Any recommendation/suggestions are welcomed.

Answer 1

Text and blob are nearly identical, the main difference is that you have to care about the encoding yourself when using blob. On the other hand you will be forced to use the encoding of a text field... Storing files in the database has some advantages and disatvantages:

Advantages:

• Backups and switches to another server are easier to achieve
• No need to add another method to access the files beside the database

Disadvantages:

• Accessing an image means retrieving it's bytes and maybe storing that as temporary local files and may be more complicated
• File systems are simply faster due to lower overhead

The decission is yours, the most opinions that I have heard so far are to store the pictures as files; but that are opinions, try to base your decision on your projects needs.