Adding the time dimension to a database

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Ideas Centre
Post Reply
User avatar
Dog Cow
Registered User
Posts: 2495
Joined: Fri Jan 28, 2005 12:14 am
Contact:

Adding the time dimension to a database

Post by Dog Cow » Mon Apr 27, 2009 7:00 pm

I read about Google's own database system which adds a dimension of time to the records which can be used for versioning. This is a little abstract for someone who is used to a 2-dimensional database such as MySQL. With these databases, you have the X-dimension being columns, and the Y-dimension being rows. But with a third-dimension such as time, now there is a Z-dimension "going back" on the X-dimension rows.

I thought this would be an interesting feature to implement for "versioning" posts. Each time a post is edited, the old post is saved, and the new post becomes the most recent version. Of course, all of the older posts are accessible as well.

I was wondering (I have a vague idea) how best to go about implementing this on a traditional RDBMS such as MySQL, and if anyone else here has some comments on the general theory and/or practice of it.
Moof!
Mac GUI Vault: Retro Apple II & Macintosh computing archive.
Inside Allerton bookMac GUIMac 512K Blog

tank71
Registered User
Posts: 23
Joined: Sun Apr 19, 2009 1:44 pm

Re: Adding the time dimension to a database

Post by tank71 » Mon Apr 27, 2009 11:32 pm

I think that this would be a great idea. However, it would be rather hard to implement.

The concept has a solid theory behind it. When it comes to phpBB, I do not think the rewards would be worth the effort.
TANK71 -- USAF Active Duty

User avatar
3Di
Former Team Member
Posts: 14477
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Adding the time dimension to a database

Post by 3Di » Tue Apr 28, 2009 12:24 am

Dog Cow wrote: I thought this would be an interesting feature to implement for "versioning" posts. Each time a post is edited, the old post is saved, and the new post becomes the most recent version. Of course, all of the older posts are accessible as well.
If I correctly recall there is already a MOD that does that, I'll try and post the link... in case.
Please PM me only to request paid works. Thx.
Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user
Extensions, Scripts, MOD porting, Update/Upgrades
✒️ Black Friday 2019 @ The Studio ▪️◾️

User avatar
Eelke
QA Team
Posts: 2903
Joined: Thu Dec 20, 2001 8:00 am
Location: NL, Bussum
Name: Eelke Blok
Contact:

Re: Adding the time dimension to a database

Post by Eelke » Tue Apr 28, 2009 8:13 am

All you need is a version column in the posts table, and instead of updating upon edits, do inserts while increasing the version number. Most queries would have to be modified to get only the most recent version of posts. I wonder how performance would be, though.

User avatar
Dog Cow
Registered User
Posts: 2495
Joined: Fri Jan 28, 2005 12:14 am
Contact:

Re: Adding the time dimension to a database

Post by Dog Cow » Tue Apr 28, 2009 1:57 pm

Eelke wrote: Most queries would have to be modified to get only the most recent version of posts. I wonder how performance would be, though.
Yeah, that's the problem: how to get the most recent post efficiently?

I wonder if storing edited posts in a separate table would be the solution, since they would not be accessed quite so often. Then you'd have a schema like:
edit_id - Primary key
post_id - Foreign key
post_version
post_text
Moof!
Mac GUI Vault: Retro Apple II & Macintosh computing archive.
Inside Allerton bookMac GUIMac 512K Blog

CMCDragonkai
Registered User
Posts: 483
Joined: Sat Jun 09, 2007 11:37 pm
Location: Australia.. and other parts of the world sometimes...

Re: Adding the time dimension to a database

Post by CMCDragonkai » Wed Apr 29, 2009 5:23 am

Yea, I could definitely make use of this... Maybe have an option for which topics/posts would have versioning...
Show phpbb threads as html articles. V.5.03 Thanks Erik! (This will be updated constantly as I or others contribute...) This code is to be used on external HTML page, but if you want a template version see here.

For the best PHPBB total modification to posting - bbcode, embedding... everything! Visit MSSTI's ABBC3 Modification.

kocureq
Registered User
Posts: 124
Joined: Sat Apr 11, 2009 7:59 pm

Re: Adding the time dimension to a database

Post by kocureq » Mon Dec 21, 2009 10:50 pm

3Di wrote:If I correctly recall there is already a MOD that does that, I'll try and post the link... in case.
Does anyone know about such mod?

Post Reply

Return to “phpBB Discussion”