backup table

BEFOR

To keep a record of edits performed, instead of the typical methods of either keeping multiple cols in a single table, or repeat rows in another table -- adding rows and showing the last result for these to appear live onsite:

Will have "livevalue", "request", "adminnotes","posted/review date" "submit date". The values and notes are all longtext values having 200+ characters per col, so this will be a large amount stored when considered altogether.

My script will place the "livevalue" both in a main table with a copy is sent to a history table along with "request" where it would be with "adminotes","posted/review date" "submit date".

This would give the advantage of keeping the main table reduced in size, more concise, opening more space to "editnotes" in the history table. Mainly, if I can keep "livevalue" in the main table where it's more central to common queries, that is why I'm doing this, and I can record particulars from an admin standpoint

But here's my question. Since it's normally best to only keep a single value in a single place on the database, this would be an exeption since the "livevalue" would be in two seperate tables at the same time, temporarily for some users.

The history table is only for the record and its values are not being used in any other way, it would be an exception to search on it.

I intend on verifiying these inserts into both tables visually with a redirect page and also with SELECTs.

Any thoughts on this backup method greatly appreciated.

sfullman

doing what you're doing is complex but fun, hmm..

I suggest you have a table with a primary key (you probably have that) and of course your column which you're going to be giving a history.

then have this table name historytable:
maintable_id (foreign key)
colname
username
sessionid
effectivefrom
effectiveto (if you want this, a bit fancy)
editvalue
editnotes
(this is where the content is stored)

then you can do this query

select
maintable.id AS id,
maintable.mycolumn AS current,
historytable.editvalue AS historyvalue,
effectivefrom AS editdate,
editnotes AS notes
FROM
maintable
LEFT JOIN
historytable
ON maintable.id = historytable.id AND
username = '$desireduser' AND
sessionid = '$thissession' AND
colname = 'mycolumn'
ORDER BY effectiveto DESC

This will give you a descending history of edits on that column PER SESSION and PER USER. You may not need session information.

Note that if the record count is one and the values in historytable are null, you immediately know that the column has not been edited.

You can even SELECT like this:

IF(historytable.editvalue IS NULL, mycolumn, editvalue) as CurrentVALUE

and add a LIMIT 1, and you'll get the most recent value.

I haven't answered everything but hopefully that gives you some ideas.

Sam

BEFOR

thanks much,

Answering this helps me to wrap up my entire admin panel. Not exactly a small task. So I do appreciate it.

In your example are the values in these two columns the same value?

maintable.mycolumn

historytable.editvalue

My main concern is whether to have the duplicate values (the one that's echoed live, in two seperate tables. I'm starting to think as long as each update is duplicated in the same way, how would it matter?

I do have a userid in the second table (primary on first main table)

sfullman

well, they're the same "field" but not necessarily the same value. You would need to analyze the data and dates of the data to see which is more recent. Also you need maintenance coding to clean up and replace the amin table value with the most recent value (also can be done by a neat query).

I first used this approach on a history table for a client who had a table of invoice and transaction items that were hard-tied to certain assets. However the price might change. Having the price again in a history table with a effectiveFrom and effectiveTo field allowed me to get the current price, and even build a history assuming monthly charges at the same time.

SQL is nice..

Sxooter

by the by, if you want to play with a system that does this IN THE DATABASE, so the application doesn't even need to be aware of it, download postgresql and look in the /contrib/spi directory.

BEFOR

silly question, but what about if I'm using phpmyadmin?
- do I use this seperately, and are you talking about running this directly from the server (on shared host)