Hi
I'm looking at using pgsql as a backend to a web CMS but could do with a little advice from the crowd on the wiseness
ofmy schema thinking.
TL;DR the design is centered around two tables "pages" and "page_content", where "pages" has a jsonb column that refers
to"page_content" in a key-value style (key for content block location ID on the web page, value for the database
lookup). Probably both, but certainly "page_content" would need to be versioned.
My present thinking is along the following lines (table columns minimised for this post):
create table pages (
page_id uuid primary key not null,
page_metadata jsonb not null
);
create table page_content(
content_id uuid not null,
content_version_id uuid not null
content_valid tstzrange not null default tstzrange(now(),'infinity'),
content_data text,
EXCLUDE USING gist (content_id WITH =, content_valid WITH && ) DEFERRABLE INITIALLY DEFERRED
);
create unique index if not exists on page_content(content_version_id);
CREATE OR REPLACE VIEW current_content AS select * from page_content where content_valid @> now();
An example "page_metadata" entry might look something like :
{
"page":"foo",
"description":"bar",
"content":[
"pageHeader":"E52DD77C-F3B5-40D9-8E65-B95F54E1C76B",
"pageMainLeft":"0BEFA002-7F9B-4A6A-AD33-CA916751B648"
]
}
So I guess my questions here are :
Am i nuts with this thinking ? Is there a saner way to do this ? Should I be using pgsql at all for this, e.g. the cool
kidswill probably say I should be using a graph database ? (N.B. I did consider a pure old-school relational model
withno jsonb, but I considered it too difficult to model the dynamic nature of the fields, i.e. unknown many-many
relationshipbetween page content locations and content ... but I'm willing to be proven wrong by wiser minds)
Then, on a more technical level .... what would an optimal query for looping through the json content array look like
? I have always been pretty useless when it comes to CTE expressions !
Thanks all