The situation is really interesting and goes something like this.

I want to create a dynamic CMS system in a PostgreSQL database. I’m fairly new to creating my own Database Schemas, so I need some help. Let’s say I have these tables.

Table Space: // Like the DB name, you can have many “DB”‘s

has composite primary key of (slug::varchar, ws_id::uuid // id of a workspace_table, does not really matter now.)

Table Model: // that is like the name of the table

has a composite primary key of (slug::varchar, space_slug::varchar) and space_slug is a foreign key to space.slug
… More Model fields like name…

Table Field: // that is like the column definitions of a table

has a composite primary key of (slug::varchar, model_slug::varchar) and model_slug is a foreign key to model.slug
… More Field fields like type of column (number, text, json)…

Table Row: // rows for the cols of the table

has a composite primary key of (folio::integer, model_slug::varchar) and model_slug is a foreign key to model.slug
… More Row fields like created_at…

Table Value: // cells of the rows

has a composite primary key of (field_slug::varchar, row_folio::integer) and field_slug points to field.slug, row_folio points to row.folio
… More Value fields like the actual value ("12","Some String like HTML or MARKDOWN","{"key":"value"}"), which is a stringified version of the actual value.

And I want to add a table for relations. Lets say I specified that the Field’s type is a referece and the Value’s valie is a text that references another row and I want to connect them with a help table like this:

Table row_to_value:

has a primary key of (from_row_folio::integer, from_field_slug::varchar, to_row_folio::integer, to_row_model_slug::varchar), which are also foreign keys as well.

• Q1: Should the from_row_folio and from_field_slug point to Value.row_folio and Value.field_slug respectively, or should they point to Row.folio and Field.slug.
  They are after all the same info and will be deleted/updated cascadly.
  to_row_model_slug and to_row_folio are the PK of the other Row

• Q2: Is this the right approach to connect rows. I want them know which Value makes the relation, and if I delete ot update the value, the relation should update aswell. (Like a real database). I also want to have the Value once and only chain relations if it’s a list.

• Q3: Is there something wrong with the other tables? I’ve posted the bare minumum of columns here, but I think it gets the job done of showing what I want to achieve with this.

• Q4: Are other CMS systems like Contentful done this way? Or are they using a different kind of database. Is my scenario really doable in SQL. I’ve made my first prototype a month ago and I stored the values as JSON in the Row Table, but that had it’s weight of problems like: searching in a JSON, filtering by a JSON field, sorting by a JSON field. filtering by a relation that doesn’t really exist, creating a stable API that shows only the fields that are enabled and discards invalid ones and so on and so forth.