Text files vs. text in database

Question

I'm designing a new site, deciding whether to keep the bulk of the content for each page in a text file, with just the file name in a database record for that page, or to keep the entire text in the database record as a string. The text is typically a few hundred to a few thousand words, with embedded markup to include photos or whatever, to be processed in PHP before being sent out as html.

I don't find enough enlightening discussion of this design choice online. What are the pros and cons of each way?

The advantage of text files, I imagine, is easy access to the file by ftp or other means, making fixing typos or editing the material easy w/o having to fuss with the database at all.

OTOH, keeping it directly in the db means I won't have to bother learning how to read text files in PHP 8P Seriously, what implications are there for maintaining the site, security, efficiency, and aspects I'm not thinking of?

Answer 1

Text files are a little bit faster and easier to handle.

Thing is... you will need to deal with databases sooner or later as developer.

I noticed you didn't mention any search features. If your texts are going to be internally searched, then there is no doubt about using db, as they count with fulltext search features built-in.

Answer 2

Store the text in the database, but cache it in files. You'll get all the benefits of both approaches. Rendering a page would go something like this:

if (a cached version of page doesn't exist) {
    generate the page content from the database
    store the page content in the cache
}
serve the page from the cache

Then, when editing a page:

store the new content in the database
wipe the cache for that page

Thus, you'll only need a single db hit, when a page changes. Your performance will be much better than a direct database store because the vast majority of your page hits are simple static files, and you'll get the benefit of central management, text indexing, etc.

Answer 3

The question is comparing:

1. Storing text in the database
2. Storing text in files and storing the filenames of those files in the database

Given this, storing everything in the database is going to not only be a lot easier, because you only have to solve the problem once, but also more robust.

Databases enforce integrity on their data. It's very safe for one process to write to the database and another to read from it at the same time. The database server uses locking to ensure that the reading process doesn't read a partially-written record.

However, if you're trying to keep the file system and the database in sync with each other, there's no way to prevent other processes from reading at the wrong moment. In your trivial example it's not likely to be a huge problem as long as your application doesn't do anything overly catastrophic if it finds an anomaly.

Backups in particular become quite a problem because it's typical for a backup of the filesystem to be out of sync with the database by a significant time, as databases are backed up using a different method.

If you can keep all variable data in a database, backups and scaling to more than one server will be easier. Of course, sometimes this is impractical - many people advocate storing data in files if it is very large.

Answer 4

Text files are much easier to work with since any system you have to edit and manage them will work seamlessly. Including them in PHP is only a matter of learning to use the include() function. You can easily use all the tools to FTP, sync and source-control directly. These advantages are huge.

If you store your text in the database you can either put them directly using your favorite DB interface which will be torture at best. You'll have trouble spell-checking, revising and entering the data (particularly at the length you are asking). Instead you will probably end up building another web-app to put the text in the database for you which will probably be a waste of time compared to the simple include() call needed.