Storing html in mysql text fields, or include files? (html formatted articles)

Question

I'm wondering if I should store long html formatted articles in my MySQL database, or if it would be better to store a reference to a file, and include that instead. It is not just paragraphs, but lists, tables, images (<img src="">'s), and other containers containing side notes and such. The articles are usually somewhere between 150 000 and 250 000 characters (including spaces). But that is excluding html tags.

It works fine storing it in database. But was wondering if there was a better approach?
There is no search options on this site.

Answer 1

I would recommend you put as little HTML in your database as possible. One very popular design pattern for websites is the Model-View-Controller pattern.

Using that pattern your data (author, title, categories, article content, etc.) would be stored in the database without HTML tags and be called the Model. Then the structure of the HTML page would be stored in a file called the View. The Controller would be in charge of accepting the HTTP requests, requesting the appropriate data from the database, and merging/sending that data with/to the view.

Any common web programming platform should be able to accommodate this pattern.

Answer 2

Plan A The HTML is already built and never changes.

Store only a url in the database, then tell the browser to go reach for that url. That, after all, is how images are done.

As with images, half the gurus will recommend storing the pages in the database, not as separate files. That's a separate debate. (It has been hashed out many times, but never really settled.)

Plan B You are creating the HTML on the fly.

In this case, you need the text accessible for assembly. It may involve dozens or hundreds of records scattered around various tables, especially since you include "lists". Now the html is just bits and pieces. You still have an important choice to make.

• If the text in the tables is only text, no markup, then call htmlentities() (or equivalent) after fetching the text and before surrounding it with html tags.
• If the text necessarily contains embedded html tags, then think of the strings as opaque blobs that you are concatenating together to build the page.
• If you try to do some of each, you are likely to stumble an make a mess.

Side note

When you have big chunks of text in a table, it will usually be more efficient to compress it in the client, then store in a BLOB.