JSON vs Foreign Keys

Question

I'm developing an enterprise website, and I have to define some sections like this:

• I have many sections and each section have many (or one) sub-pages.
• The number of sub-pages vary per section.
• Each sub-page should not be longer than 2500 characters

Also, I have to use a relational database (MySQL).

I'm in doubt which would be the better in terms of design and/or performance:

1. To store a JSON array, with all sub-pages as object, as a single column of the Sections table;

2. To create a table Sub-pages which references it's parent section by a Foreign Key.

Answer 1

I would go with 2.

create a table Sub-pages which references it's parent section by a Foreign Key

When you have to do such Sections Sub-pages like things then I would go with creating separate tables so that it becomes more efficient in organizing the data.

There's one scenario that I know which I would like to tell you. If you store all the sub-pages as object in one column of a section table, it becomes more complicated in terms of maintaining. When someone wants to edit the contents you have to read all those data and then make calculation with indexes and so on.. Honestly I would consider it as a bad design. And then performance wise it works but might take some time in reading itself. So better to stick with second one by using the best relational models with RDBMS. Hope this helps.

Answer 2

It is important that your model and your DB use the same paradigm, else you have too much "frictional loss". So if your data don't fit in the relational schema, you might consider to switch to a NoSQL DB (like Mongo DB).