Storing form data as JSON

Question

I've read a lot about the limitations of storing json and query speeds, however, I'm still struggling to design my DB.

Currently, I'm storing the data from a CMS (a form with inputs) as JSON in a column content

The stored data looks like this:

{
  "heading": {
    "value": "Taylor Francis"
  },
  "sub_heading": {
    "value": "photographer \/ artist \/ designer"
  },
  "button_caption": {
    "value": "View Work"
  },
  "button_link": {
    "value": "/work"
  },
  "content": {
    "value": "<p style=\"margin-top: 22px; margin-bottom: 22px; padding: 0px;\">A Brief History[...]"
  }
}

This data only needs to be accessed and parsed when the user updates the page.

Upon saving the page a cached HTML version is created so no speed issues when reading the page for displaying I think.

My question is will that create more problems in the future? I really don't need to search this data or anything. Just allow the user to edit it once in a while (and add more elements to it if needed)

Or should I just convert it to key->value pair "meta" table and do joins on the page id?

Or, alternatively, I've thought of adding column for every possible input: heading column, sub_heading column, button_caption column etc.

Thanks!

Answer 1

You probably have a good design. However, I would consider getting rid of the "cached HTML version". You can probably 'build' the page from the JSON "fast enough". Look at this stackoverflow page. It is not cached. In fact, it is built from a large number of components, many of which are probably loaded dynamically from a database.

Having a "column for every possible input" would quickly become a nightmare for maintenance. Make columns for things you need to search on; use JSON for other stuff and let the application deal with parsing/expanding/etc it. That is, don't go to EAV (Entity-Attribute-Value) design.