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?

Solution 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.

Solution 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.

Solution 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.

Comments

[thomasrutter]

You would not use the include() function to read text files from the file system, as it would execute any PHP in them. Also, the question refers to storing the text in files and the filenames of the files in a database, which doesn't sound very easy to work with.

[markof]

@trr - That is an advantage actually. I use it all the time. Most of my content is stored in text files which contain mostly HTML markup but also PHP to get some site features working throughout the content. Simple read the database row with the filename and pass it to include() and voila!

[DarenW]

The text files I have in mind are neither html nor php (and not anything else recognizable to normal web developers) but a special markup I concocted, to be processed by a special php function. I'd be loading the file contents into a string var.