How to store articles with photos and formatting in a database?

Question

Take a look at some of the crease articles, such as this one . They are beautiful and rich in photographs, which makes them attractive, but these are not just articles. They have photographs and other extraneous components, which makes them more difficult, from my point of view, to store them. In short, what is the best way to create a similar implementation of an article for a personal website or project at the database level?

By this, I mean, say, I have to write an article that is divided into 8 paragraphs, some of the paragraphs are children from <section> , and between these sections there will be different photos, various layouts - a parallax photo, a full photo and a style viewer galleries, for example, perhaps even quotation marks. This creates a fairly complex HTML structure that looks more like a simple text article that can be saved simply as text in a database and then simply displayed between two tags on a web page. How can I store all this information, HTML tags, class names, etc. In the appropriate database and application?

I came up with a few ideas, but I'm not quite sure about the best practice or about what advantages and disadvantages each option has.

Option 1. Store everything as plain text in the database field.

It is the simplest but the ugliest. Everything, image tags, class names and all of them are stored in text form inside the article_text field inside the article table.

Option 2. Save the text of the article and formatting inside the database field, then save the images in another table.

This is a hybrid solution of 1 and 3. In principle, you should refer to the image section inside the article as follows:

 {{ imageSection1 }} 

let's say and get the application logic for integration and drag it into the final product. It is easy on the database side, but becomes more confusing on the user side.

Option 3: Store everything separately.

Each paragraph is its own entry in the article_collation table with images and comments and quotation marks stored in their own independent tables. This seems like the most efficient way to separate individual elements that need to be stored separately, but makes software logic infernal and may be less efficient because of it.

---

Each of us has serious problems with IMO, and I'm not sure what to do. Input? Recommendations? Are there any tools to facilitate this?

Answer 1

I'm a big fan of using XML and then dynamically converting it to your output format (using xslt or the like). This allows you to present your content in a strictly structured way, but there is still something that could be referenced. This helps in things like versioning, using tools like diff, or even packing them, and moving them into lists of files (rather than database tables that need to be reassembled).

It also allows you to use completely different applications for your content in some future system that is not based on HTML (for example, in PDF format).

Take a look at Martin Fowler's article: http://martinfowler.com/articles/writingInXml.html

There are several open “standards” for publishing content in XML that are worth paying attention to. NLM is pretty common. I have experience with it, and I found it very (possibly complete).

Answer 2

I would choose Option 3: Keep everything separate . It will be challenging, but most flexible. It allows you to deploy CMS one bit at a time.

Rough outline of table structure:

 article - article_id (PK) - title display - display_id (PK) - name section - section_id (PK) - article_id (FK) - display_id (FK) - sort content - content_id (PK) - section_id (FK) - title - description - image - sort 

Example values ​​for the display table to start with:

 1: pass-through 2: horizontal-slideshow 3: vertical-sections 

And here is an example of what the page looks like at the end (classes and identifiers will show how the data looks):

 <div id="article-1" class="article"> <h1 class="title">Article Title</h1> <div id="section-1" class="section display-pass-through"> <div id="content-11" class="content"> <h2 class="title">Introduction</h2> <div class="description">Lorem ipsum dolor sit amet.</div> </div> </div> <div id="section-2" class="section display-horizontal-slideshow"> <div id="content-21" class="content"> <h2 class="title">Slide 1</h2> <div class="description">Lorem ipsum dolor sit amet.</div> <img class="image"> </div> <div id="content-22" class="content"> <h2 class="title">Slide 2</h2> <div class="description">Lorem ipsum dolor sit amet.</div> <img class="image"> </div> <div id="content-23" class="content"> <h2 class="title">Slide 3</h2> <div class="description">Lorem ipsum dolor sit amet.</div> <img class="image"> </div> </div> <div id="section-3" class="section display-vertical-sections"> <div id="content-31" class="content"> <img class="image"> <h2 class="title">Heading 1</h2> <div class="description">Lorem ipsum dolor sit amet.</div> </div> <div id="content-32" class="content"> <img class="image"> <h2 class="title">Heading 2</h2> <div class="description">Lorem ipsum dolor sit amet.</div> </div> <div id="content-33" class="content"> <img class="image"> <h2 class="title">Heading 3</h2> <div class="description">Lorem ipsum dolor sit amet.</div> </div> </div> <div id="section-4" class="section display-pass-through"> <div id="content-41" class="content"> <h2 class="title">Conslusion</h2> <div class="description">Lorem ipsum dolor sit amet.</div> </div> </div> </div> 

You can add additional display options as your requirements grow (one movie and a movie playlist are two examples). Use semantic markup to display content no matter which section it belongs to. You can use the JavaScript and CSS classes to control the viewing of sections (for example, for a horizontal slide show, you can hide everything except the first slide with CSS and add previous / next controls using JavaScript).

Note. The above idea can be generalized to use only one self-reference table. Summarizing too many and / or using self-regulatory tables has its problems. However, there will be no restrictions on how and how deeply content can be embedded; very similar to how you create HTML content.