Temporal database with references

[Highlander]

Hello!

 

I'm trying to build a blog and use a database design that can record history using temporal data.

 

I have a user table that looks like this:

CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT,
username varchar(40) NOT NULL,
password varchar(32) NOT NULL,
first_name varchar(40) NOT NULL,
last_name varchar(50) NOT NULL,
-- Temporal columns
st timestamp NOT NULL DEFAULT now(),
et timestamp NULL DEFAULT NULL,

PRIMARY KEY(id)

) ENGINE=innodb CHARSET=utf8;

 

Now I want to have a blog_post table with a author id connected to the user table, but how do I uniquely store a identifier to the user?

 

My first though was to store ID + st in blog_post table, but it doesn't seem right.

 

Any hints on this?

 

[JonnoTheDev]

blog_posts

========

blog_id

user_id

blog_content

 

blog_posts.user_id = user.id

[Highlander]

blog_posts

========

blog_id

user_id

blog_content

 

blog_posts.user_id = user.id

 

But this will not work, since the ID will change every time a change is made to the user row (id column has auto_increment), since a new row is inserted for every change so that you can track each change.

 

Hm, I think I need to read more about this.

 

 

 

[JonnoTheDev]

But this will not work, since the ID will change every time a change is made to the user row (id column has auto_increment)

You would never update a primary key. It is used in the WHERE claus of the update query to define what record to update

UPDATE users SET name='joe', email='joe@123.com'  WHERE userId='123'

 

I suggest you spend some time on database normalisation

[Highlander]

But this will not work, since the ID will change every time a change is made to the user row (id column has auto_increment)

You would never update a primary key. It is used in the WHERE claus of the update query to define what record to update

UPDATE users SET name='joe', email='joe@123.com'  WHERE userId='123'

 

I suggest you spend some time on database normalisation

 

Yea, this is the case for normal database with no temporal support. In temporal database you need an ID for each update to the object in a unique row.

 

After some thinking and help from a friend, here is the solution for the database schema:

 

-- Author: Johan Grahn
-- Database schema for my blog

DROP DATABASE IF EXISTs high_blog;
CREATE DATABASE high_blog CHARACTER SET = utf8;

USE high_blog;

CREATE TABLE user (
        username varchar(40) NOT NULL,
        password varchar(40) NOT NULL,
        first_name varchar(40) NOT NULL,
        last_name varchar(50) NOT NULL,

        id int NOT NULL,
        revision int NOT NULL DEFAULT 1,
        st timestamp NOT NULL DEFAULT now(),
        et timestamp NULL DEFAULT NULL,

        PRIMARY KEY(id, revision)

) ENGINE=innodb CHARSET=utf8;

CREATE TABLE blog_entry (
        id int NOT NULL,
        revision int NOT NULL DEFAULT 1,
        title varchar(100) NOT NULL,
        content text NOT NULL,
        st timestamp NOT NULL DEFAULT now(),
        et timestamp NULL,

        author int NOT NULL,
        author_revision int NOT NULL,
        PRIMARY KEY (id, revision),
        FOREIGN KEY (author, author_revision) REFERENCES user(id, revision)

) ENGINE=innodb CHARSET=utf8;

 

In this schema, I store each update to the id in a unique revision so that each user with a given ID can have multiple revisions (each revision contains the updated data for the user).