NYCPHP Meetup

[Mark L. Withington]

Thanks for your quick response Adam.  Only concern might be the size of the
database as go forward.  Each record might contain 100+ fields, not all of
which would change during each iteration.  I therefore, might be saving
redundant information (e.g. only two or three fields might change over the
different versions).

I was thinking of creating a "before" and "after" array, and then storing
the serialized difference in another field.  This will obviously require a
bit more logic to reconstruct prior versions, but in exchange would minimize
storage requirements.

Anyone see any flaws in my logic?

--------------------------
Mark L. Withington
PLMresearch
"eBusiness for the Midsize Enterprise"
PO Box 1354
Plymouth, MA  02362
o: 800-310-3992
f: 508-746-4973
v: 508-746-2383
m: 508-801-0181
http://www.PLMresearch.com
Netscape/AOL/MSN IM: PLMresearch
mwithington at plmresearch.com
Public Key: http://www.PLMresearch.com/keys/MLW_public_key.asc
Calendar: http://www.plmresearch.com/calendar.php


-----Original Message-----
From: talk-bounces at lists.nyphp.org
[mailto:talk-bounces at lists.nyphp.org]On Behalf Of Adam Maccabee
Trachtenberg
Sent: Tuesday, April 27, 2004 1:58 PM
To: NYPHP Talk
Subject: Re: [nycphp-talk] MySQL versioning system


On Tue, 27 Apr 2004, Mark Withington wrote:

> I have a client who would like to store product development information
> within a MySQL table.  The table will consist of multiple fields, each
> representing a product attribute (height, weight, color, etc.).  The
client
> would like to store each iteration (or version) of the product development
> information along with the ability to role back to prior versions if
> necessary.

My first idea would be to create a unique identifier for each product
and store all the product specifications alongside the identifier and
a version number. When you select the specifications for a product,
select the one with the highest version number. Rolling back only
requires you to delete the highest version number from the database.

The easiest version number is either an auto_increment field or maybe
a timestamp of the insert time.

Also, if you were super concerned about select speed, you could make
an additional column that's an enum('current', 'old') and make the
latest version as "current" and the others as "old". Of course, this
requires more convoluted insert / update logic. But this may or may
not be necessary.

-adam

--
adam at trachtenberg.com
author of o'reilly's php cookbook
avoid the holiday rush, buy your copy today!
_______________________________________________
talk mailing list
talk at lists.nyphp.org
http://lists.nyphp.org/mailman/listinfo/talk