so
  • This post is part of a thread:
  • (ye)SQL, 28 Dec 2020

SQL solution

Volume 4, Issue 48; 31 Dec 2020

The solution I settled on was a single database with two versions of the “main” table.

Just completing the loop here. My previous post spawned a short thread on Twitter. The TL;DR for that thread is, “you’re doing it wrong.”

Which I think is a fair criticism. I’m woefully inexperienced with relational technologies.

I don’t think I really explained the problem I was trying to solve very clearly. And I’m not going to try again now (though I might at some point in the future). This weblog is that problem, FWIW.

I took a step back and decided that although there are about eight tables involved, there’s only one that really matters: the table that contains the prose of the posting. That’s the table that needs to be held back for editorial review. If some of the other tables are a little bit out of sync (if, for example, the subjects or topics or GPS locations associated with a post aren’t exactly right), it won’t matter very much and no one will ever notice anyway.

What’s working today is the following: creating or editing posts only ever puts data in the “staging” table. When I’m logged in, I can run queries against the staging table and preview it. There’s a trigger on the staging table that keeps a timestamp up-to-date on each row.

“Publishing” consists of finding all the rows in the staging table that are newer than (or don’t exist in) the “production” table and copying them over. The production table can only be updated in this way, so it should remain entirely consistent.

Time will tell, I suppose.