5

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.

2
  • What is the problem you're trying to solve by storing HTML in the database? Why not serve the file directly?
    – Erik
    Sep 3 '15 at 18:39
  • I would have to store some article data i a database either way because of accociated details (author, category, title, query url etc.) But was simply wondering if I should put the html-formatted article body in its own file, and request that file instead of having it directly in database?
    – ThomasK
    Sep 3 '15 at 20:15
2

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.

3
  • Thanks. I'm familiar with MVC. This platform does not use it though, but regardless of that, you answered my question.
    – ThomasK
    Sep 3 '15 at 21:31
  • 1
    @ThomasK in that case you have my sympathy. I had a coworker that had to maintain a system that used protocol buffers and recreated MVC while storing big HTML strings in the database. It was an opaque nightmare. I hope your project goes better. I also apologize if I seemed patronizing. That wasn't my intent.
    – Erik
    Sep 3 '15 at 21:35
  • No worries. Always good to get input regarding coding patterns and such for better usability...
    – ThomasK
    Sep 3 '15 at 21:55
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.