From: | Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> |
---|---|
To: | postgre <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Modelling a web CMS in Postgres ... a little advice needed |
Date: | 2022-08-11 07:35:22 |
Message-ID: | 6NtGy9Qbxtvte6bfGm7s85U2rAzIr90N6UhrCfs_ca4kd_ol6MlhZ0RsMsASDduc4hIwzf7VroZj9ZRotrsLO5P62EgXpuxJ2BH7DvdjJlM=@protonmail.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 of my 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 kids will probably say I should be using a graph database ? (N.B. I did consider a pure old-school relational model with no jsonb, but I considered it too difficult to model the dynamic nature of the fields, i.e. unknown many-many relationship between 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
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2022-08-11 08:10:56 | Aw: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario |
Previous Message | Bryn Llewellyn | 2022-08-11 05:05:44 | Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario |