ainsley_clark's avatar

Database Schema Review / Questions for CMS

Hi there,

I'm in the mist of creating a Laravel application that will closely mimic Wordpress. The thought process is quite similar. You are able to create resources from a config.json file that sits in the themes directory add custom fields with flexible content (similar to that of Advanced Custom Fields).

As this is one of my first big builds I have a couple of questions regarding fields & categories:

Categories:

A developer is able to add a resource (such as news, or posts) and each resource can have a category which is labelled and inputed into the category table. A category can belong to more than one resource. What I can't wrap my head around is how to separate these two tables (categories and category content), what might feature in a different table and what I might even name it!

Fields

An administrator is able to create field groups that can belong to resources or pages. A field group would contain an array a fields, which would contain data such as type (richtext, name, required, conditional logic etc..). How would this data look in a schema? Would would be best pratice?

Any help would be greatly appreciated, I'm new to this world!

Please feel free to provide any constructive criticism regarding this design.

Many thanks.

EDR Diagram can be found here - CMS - SqlDBM Link

CMS Schema

0 likes
2 replies
fylzero's avatar

@ainsley_clark Look into polymorphic relationships for your categories.

It is likely exactly what you need here. It sounds more complex than it really is.

Basically many to many relationship but with a dynamic model. Think of photos. You can have user photos, product photos, location photos... all models that many photos can belong to and can have many photos. So youre basically making a pivot type table where it points to which model you are referring to.

This course really helped me understand this.

https://laraveldaily.teachable.com/p/laravel-eloquent-expert-level/

Not knowing what you category content is makes it difficult to decide what to name it.

For dynamic field groups... you could just stash this in a json field in the database. It is a clean approach for this type of thing because everything lives in one block and you don't have to do a bunch of juggling in the database... also, if this is just for field template creation that usually doesn't need to be searchable. That said, there are tricks to searching json strings in the db if needed.

So just make a json type field for form_fields and build it out however you see fit...

{
    'field_1': {
        'type': 'input',
        'name': 'item-content',
        'placeholder': 'Item Content'
    },
    'field_2': {
        'type': 'textarea',
        'name': 'item-description',
        'placeholder': 'Description'
    },
}
24 likes

Please or to participate in this conversation.