Temporal Database Design, with a Twist (Live VS Draft Rows)

Temporal database design, with a twist (live vs draft rows)

I think I nailed it. Basically, you add a (unique) draft field to the relevant tables, and you work on the drafts as if they were a new post/tag/etc.:

posts (
 id pkey,
 public,
 created_at stamptz,
 updated_at stamptz,
 updated_by int,
 draft int fkey posts (id) unique
)

post_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by,
 draft fkey tags (id) unique
)

tag_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by,
 pkey (post_id, tag_id)
)

post2tag_revs (
 post_id,
 tag_id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_id, tag_id, created_at)
)

Should draft records be kept in a separate table?

No. One entity type, one table.

Reasons to reconsider:

1. Draft records outnumber live records by a factor of thousands to one.

2. Security conditions require that some users who access the database directly have certain rights over draft or live records at the GRANT/REVOKE level but not over the other type of record.

A second design to consider would be one table for the Items and a second table for LiveItems. The second table contains only the IDs for the Items that are live. That way you're maintaining your single table design, but you can find the LiveItems by joining your one-column table back to the main table.

Database Design with Change History

One way is to have a MyTableNameHistory for every table in your database, and make its schema identical to the schema of table MyTableName, except that the Primary Key of the History table has one additional column named effectiveUtc as a DateTime. For example, if you have a table named Employee,

Create Table Employee
{
  employeeId integer Primary Key Not Null,
  firstName varChar(20) null,
  lastName varChar(30) Not null,
  HireDate smallDateTime null,
  DepartmentId integer null
}

Then the History table would be

Create Table EmployeeHistory
{
  employeeId integer Not Null,
  effectiveUtc DateTime Not Null,
  firstName varChar(20) null,
  lastName varChar(30) Not null,
  HireDate smallDateTime null,
  DepartmentId integer null,
  Primary Key (employeeId , effectiveUtc)
}

Then, you can put a trigger on Employee table, so that every time you insert, update, or delete anything in the Employee table, a new record is inserted into the EmployeeHistory table with the exact same values for all the regular fields, and current UTC datetime in the effectiveUtc column.

Then to find the values at any point in the past, you just select the record from the history table whose effectiveUtc value is the highest value prior to the asOf datetime you want the value as of.

 Select * from EmployeeHistory h
 Where EmployeeId = @EmployeeId
   And effectiveUtc =
    (Select Max(effectiveUtc)
     From EmployeeHistory 
     Where EmployeeId = h.EmployeeId
        And effcetiveUtc < @AsOfUtcDate) 

Blog post revisions with moderation step database design

Per your question in the comments, I would structure the review history like so:

create table article_review_status (
  article_review_status_id int generated always as identity,
  status_code text not null unique
);

create table article_review (
  article_review_id int generated always as identity,
  article_revision_id int not null 
    references article_revision(article_revision_id),
  article_review_status_id int not null
    references article_review_status(article_review_status_id),
  entry_at timestamptz not null default now(),
  article_review_comments text,
  reviewer_id int not null
    references users(user_id) -- <-- Would this be another author or an admin?
);

Then your application can determine whether to make the article visible.

Audit logging for Products / Options / Exras tables?

I don't think you've given us enough information to answer your question. Where I work, staff change data in the database every day. We keep history for some tables, and not for others. We keep an audit trail for some tables, and not for others. For some tables, we keep neither a history nor an audit trail.

I can imagine changing a product's name to correct a typo. That kind of change wouldn't usually require any changes to options or extras.

I can also imagine changing a product's name in a way that makes it a different product, and that would probably require changes to options and extras.

---

Since most companies sell products, I think you should reconsider the names of your tables. Your "products" table seems more like a category of products. Your "options" table seems more like products. Categories don't have prices, but products do.

You also asked about orders. Changes to values in your tables "products", "options", and "extras" should have no effect on previous orders. Five years from now, you might have to go to court and provide a copy of the order. If you're missing the paperwork, your database better be able to reproduce exactly what the customer paid for, even if it's full of typos and wrong prices.

---

Related Topics