Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

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.

share|improve this answer
add comment

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.