Form State/Diff/History Tracking/Deltas, PHP, & Databases

dalecosp

Good Friday to you! I hope we can make this into a helpful thread that will provide some education for all of us in the future.

I'm looking into a concept where multiple users can edit and save a form in turns. The forms would all be the same, with a number (one or two dozen, maybe?) fields that would be editable. At some point, they'd decide they were finished and we'd do Other Stuff(tm) with it.

While they're working on it, though, we'd want to be able to keep a revision history of the document/form --- what has changed, who changed it, etc.

What's the best practice? Do I keep the entire form as a file and use some sort of external library? Do I have a history table that records the changes and try and roll my own diff engine? Can I place all the fields in an array and use array_diff()?

Has anyone done something like this before? What did you learn? What would you do better the next time you have to implement this?

NogDog

For clarification: when you say "form", can we just substitute the word "document", as in multiple users are editing the same document; or are we talking about a HTML form to which they are inputting data (i.e. editing the form field content, not the form itself)?

Derokorian

I've done this in the past by keeping a history table. Since we all know I'm good with words, I'll give an example instead. Given a Post table like this:

Table: post
post_id int primary key,
title varchar(255),
body text,
uid int,
created datetime,
updated datetime

I would then create a history table like:

Table: post_history
post_history_id int primary key,
changed datetime,
changed_by int,
change_type enum('create','update','delete'),
post_id int,
title varchar(255),
body text,
uid int,
created datetime,
updated datetime

Then all changes get 2 sql's ran one for the post table, and one for the post_history table. To the history table, we just insert the current version of the object. To see what changed you just look for the differences between the change you're looking at and the most recent change before that (or any particular previous change).

dalecosp

NogDog;11056621 wrote:
For clarification: when you say "form", can we just substitute the word "document", as in multiple users are editing the same document; or are we talking about a HTML form to which they are inputting data (i.e. editing the form field content, not the form itself)?

The latter; the forms/documents will all the same except for the different values in the form fields.

Derokorian;11056623 wrote:
I've done this in the past by keeping a history table. Since we all know I'm good with words, I'll give an example instead. Given a Post table like this:
Table: post
post_id int primary key,
title varchar(255),
body text,
uid int,
created datetime,
updated datetime
I would then create a history table like:
Table: post_history
post_history_id int primary key,
changed datetime,
changed_by int,
change_type enum('create','update','delete'),
post_id int,
title varchar(255),
body text,
uid int,
created datetime,
updated datetime
Then all changes get 2 sql's ran one for the post table, and one for the post_history table. To the history table, we just insert the current version of the object. To see what changed you just look for the differences between the change you're looking at and the most recent change before that (or any particular previous change).

That's kind of what I'd been considering, but the DB design seems complex. Say we have 20 form fields ...

Table: post_history
post_history_id int primary key,
changed datetime,
changed_by int,
change_type enum('create','update','delete'),
post_id int,
title varchar(255),
field1 int,
field2 varchar(128),
field3 tinytext,
field4 enum('foo','bar'),
field5 tinyint,
field6 text,
field7 blog,
field8 int,
field9 int,
field10 int,
field11 int,
field12 int,
field13 int,
field14 int,
field15 int,
field16 int,
field17 int,
field18 int,
field19 int,
field20 int,
uid int,
created datetime,
updated datetime

The other thing will be a dialog/chat transcript about the changes. I don't see that as being terribly difficult; mostly the idea of the diff. Would you still keep a history table even if you have this many variables? (I'll admit, I can't really thing of anything else off the top of my head, either ...)

dalecosp

I suppose I had considered storing the vars as a serialized array ...

NogDog

Or a separate relational table for the answers:

form_id
user_id
field_id
value
timestamp -- use this to get latest value

Derokorian

If you store as a serialized array then you cannot easily look for specific field changes by querying for rows that have diff on a specific field. In the past, I've had tables as large as 187 columns that had their own history table. The benefit of making the table identical + history columns is you can just write the hook in a base model (something along the lines of: does this table have a history table? then insert a new row, without having to manage the data at all).

Here's a simple example of looking for posts with a title change:

SELECT post.* 
FROM post_history h1 
JOIN post_history h2 
    ON h1.post_id = h2.post_id 
    AND h1.changed <> h2.changed 
JOIN post p 
    ON p.post_id = h1.post_id 
WHERE h1.title <> h2.title

dalecosp

NogDog;11056629 wrote:
Or a separate relational table for the answers:

form_id

user_id

field_id

value

timestamp -- use this to get latest value

Hmm. "field_id" ... might be quite helpful ... interesting, at least, how that would work.

Derokorian;11056631 wrote:
If you store as a serialized array then you cannot easily look for specific field changes by querying for rows that have diff on a specific field. In the past, I've had tables as large as 187 columns that had their own history table. The benefit of making the table identical + history columns is you can just write the hook in a base model (something along the lines of: does this table have a history table? then insert a new row, without having to manage the data at all).

Here's a simple example of looking for posts with a title change:
SELECT post.* 
FROM post_history h1 
JOIN post_history h2 
    ON h1.post_id = h2.post_id 
    AND h1.changed <> h2.changed 
JOIN post p 
    ON p.post_id = h1.post_id 
WHERE h1.title <> h2.title

Yeah, I know about the problems with seralization and search.

I'm just scared how long it might take to write queries with 187 fields :o

In your query, I'm not sure I'm following why you're aliasing the post_history table to two different aliases? I'll go back up and put together some tables based on your earlier post and see if I can grok what you're saying there.

I also have to think about how the chat might relate to this ... sounds like some more joins in the query. Not unexpected, or tremendously difficult 🙂

some_app wrote:

13:10:05 Bob:  I think it should be pink.
13:10:12 user Bob changed "Color" to "pink"
13:14:30 Harry: No, BLUE!!!
13:14:35 user Harry changed "Color" to "blue"

Lastly, we're pretty much looking at a standard PHP/SQL architecture. Do we have consensus that this is the probably best solution?

NogDog

FWIW, my day job consists of working on a review system (patients reviewing medical providers), which runs in PHP (with an in-house framework) with PostgreSQL. We're in the process of re-engineering it in Ruby/Rails -- and finding a fair number of places where we have to write explicit SQL, as opposed to just using Active Record. 🙂

We don't have to deal with the multi-user aspect you're dealing with here, but we do have separate review, question, and answer tables, where the answer table has FK's to the other two.

Derokorian

dalecosp;11056633 wrote:
I'm just scared how long it might take to write queries with 187 fields :o

No reason to be scared, it takes me 0 time to write queries like that. My models are built in such a way that I don't write inserts or updates ever (or create tables, or update tables), and it automatically goes to a history table if the flag is set on the model, so I don't write those queries. Basically I've managed to get to a point I only write queries for selects when joins are involved.

dalecosp;11056633 wrote:
In your query, I'm not sure I'm following why you're aliasing the post_history table to two different aliases? I'll go back up and put together some tables based on your earlier post and see if I can grok what you're saying there.

The point of joining on itself is to compare 2 different versions in the query, to look for posts whose title have changed. So if a post has only been created and not edited there will be only one row and such no join possible. If it has been updated once, it will join the create to the update, and then check if the titles on the 2 rows are equivalent. But the point was I was showing how easily you can search for posts who've had a certain field updated. If you want to look for any field you'll want to build that query automatically because it will be like h1.{field_name} <> h2.{field_name} OR... repeating for all columns. That's obviously easy to build dynamically if you're using a good modeling system.

Of course you can simplify the search even more, if you only perform updates on records who have changed, then you know automatically that every update in the history table contains at least one change.

dalecosp;11056633 wrote:
I also have to think about how the chat might relate to this ... sounds like some more joins in the query. Not unexpected, or tremendously difficult 🙂

Not sure I follow, that seems like a different system possibly triggered by the system changing the item in the first place. but then it seems easy to find the max history id for the item before inserting the history row, then comparing the new row to that row and dispatching a chat action from that.

dalecosp;11056633 wrote:
Lastly, we're pretty much looking at a standard PHP/SQL architecture. Do we have consensus that this is the probably best solution?

Yes, most definitely.

dalecosp

I'd like to thank you outstanding gents for your time and thoughts. I'll be sitting down to outline a data structure this week, and will update you with my impressions and any other pertinent info. :-)

If you're ever in SW MO, gimme a holler and we'll find a good pizza joint!

Derokorian

dalecosp;11056671 wrote:
If you're ever in SW MO, gimme a holler and we'll find a good pizza joint!

Same to you if you're ever near Denver (well I live almost an hour North, but go down to the city from time to time).