Dynamic content storage approach for flexible website modules - MySQL schema question

Other Questions MySQL
1

I’m working on a content management system that needs to handle different types of page layouts without creating tons of database tables. My main problem is that most websites have pages that look similar but contain different fields.

For example, some pages might need a title and text, others need title, image, and gallery, and some might need completely different combinations of fields.

Right now I’m thinking about using two main tables:

table layout_definitions
page_id int
field_structure -- not sure what format to use here

table page_content  
page_id int
layout_reference -- links to layout_definitions
actual_data -- stored content, format unknown

My goal is to avoid having separate tables for every content type, but also avoid having tables with dozens of columns for every possible field combination.

I’m considering storing the structure info and actual content as JSON or serialized data, but I’m worried this might be inefficient or hard to query later.

Has anyone built something similar? What are the performance implications of storing structured data this way versus traditional normalized tables? Is this approach maintainable in the long run?

2

been there, done that mistake lol. json storage sounds tempting but querying becomes a nightmare later when you need to search specific content. i went with EAV pattern instead - entity_id, attribute_name, attribute_value table. bit more complex but way more flexible than json and still queryable.

3

I actually implemented a hybrid approach that worked well for a similar CMS project. Instead of pure JSON storage, I used a base table for common fields (title, slug, created_date) and a separate key-value table for dynamic content. The key-value table had columns: page_id, field_name, field_type, field_value, and field_order. This gave me the flexibility to handle different layouts while keeping searchable fields in proper columns. Performance was decent because I could index the field_name and field_type columns. The main downside was having to rebuild the page content from multiple rows, but caching solved most of that overhead.