Jump to content

Temporal database with references


Highlander

Recommended Posts

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?

 

Link to comment
Share on other sites

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.

 

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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).

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.