How to design a database that stores large json objects

by RobotEyes   Last Updated November 14, 2017 18:05 PM

I'm wondering the best approach to designing a database for storing crossword json data. As the website is at the moment, the frontend view just needs to be passed the whole object to render the crossword:

{
    grid: [15][15], // 2D array of letters
    clues: {
        across: { 
            clue_id: "here's an across clue" 
            ... 
        }, // Arbitrary number of clues
        down: {
            clue_id: "here's a down clue" 
            ... 
        } // Arbitrary number of clues
    }
}

What are the most sensible options when designing the database?

My instinct is to use MySQL or Postgres and just store the json data in a single column:

crossword _id | crossword_data

In the future users might be able to 'favorite' clues. In this case is it better practice to separate out all the grid and clues to different tables?

Crosswords Table [has many clues]
-----------------------------------
| crossword_id   | crossword_grid |
-----------------------------------


Clues Table [has one crossword]
----------------------------------------------------------
| clue_id   | clue_direction | clue_text  | crossword_id |
----------------------------------------------------------

Or would it be better to store the crossword data in a separate NoSql database?

Any direction on the matter would be much appreciated.



Related Questions






Elasticsearch and relational database combination

Updated March 05, 2016 01:02 AM