0

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.

  • 2
    You are probably overthinking this. In your question, there is nothing which deserves IMHO the attribute "large" (or at least, nothing large enough to make the decision between those db types important). Pick the solution you know best / which lets you implement things in the most simple way. – Doc Brown Nov 14 '17 at 17:27
  • mongodb or couchdb work with documents that are json objects, but storing it this way may not be the best option, and you certainly don't have to go nosql. Consider breaking the problem up and storing pieces. – Austin_Anderson Nov 14 '17 at 22:30
  • 1
    Just store the entire object. These crosswords seem entirely independant of each other so the things you gain from splitting them up like constraints and indeces does does not apply here. – Esben Skov Pedersen Nov 15 '17 at 6:06
1

What you are doing is a good enough approach. I did something similar with doxdb which is a ID mapped to a blob. My blob is a BSON a binary representation of JSON for optimization then have separate tables that get created on updates of the record.

In a sense that’s how Apple mail does it except instead of BLOBs they used message files and built the database on top of it.

A nosql document database like elasticsearch and mongo can be used as your data store and have a MySQL to provide the tables that you can use to query.

0

I think the decision between NoSql or relational DB is not really important here, but if you go the relational DB route, here are some suggestions.

If your use case involves to let the user create their own crosswords using predefined words and clues, I would model it this way:

Crossword Table [one crossword per row]
------------------------------------------------
| crossword_id   | clue_text  | crossword_text |
------------------------------------------------

I don't see a compelling reason to normalize this further in a way where you can have more than one clue per crossword. Words and their clues can probably handled as an atomic unit and edited always together as a pait.

Their placement or direction is only important in context of a grid:

Grid [one row per crossword grid]
------------------------------
| grid_id  | size_x | size_y | ... (maybe a column to distinguish different grids)
------------------------------

and

Placement Table [holds crosswords as well as their positions in the grid]
--------------------------------------------------------
| crossword_id  | grid_id | pos_x  | pos_y | direction |
--------------------------------------------------------

Here, direction can be an integer or character field for representing the two values across and down.

If you think you need this, you can add a letters column to the Grid table, but that will be redundant to the already modeled data.

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.