Highlander Posted August 11, 2009 Share Posted August 11, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/169721-temporal-database-with-references/ Share on other sites More sharing options...
JonnoTheDev Posted August 11, 2009 Share Posted August 11, 2009 blog_posts ======== blog_id user_id blog_content blog_posts.user_id = user.id Quote Link to comment https://forums.phpfreaks.com/topic/169721-temporal-database-with-references/#findComment-895547 Share on other sites More sharing options...
Highlander Posted August 11, 2009 Author Share Posted August 11, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/169721-temporal-database-with-references/#findComment-895842 Share on other sites More sharing options...
JonnoTheDev Posted August 11, 2009 Share Posted August 11, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/169721-temporal-database-with-references/#findComment-895992 Share on other sites More sharing options...
Highlander Posted August 13, 2009 Author Share Posted August 13, 2009 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). Quote Link to comment https://forums.phpfreaks.com/topic/169721-temporal-database-with-references/#findComment-897300 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.