Re: Re: BarcodeDB.com - a Barcode Database

On Tue, Oct 14, 2008 at 9:56 AM, Alex Chamberlain <php@alexchamberlain.co.uk> wrote: > Thanks for your suggestion!! Sounds like a good route to go down with the API access. Really > struggling with the pending 'list' at the moment. Suppose I have got 2 tables > product and description. > product contains EAN, > name, last_modified and > description contains EAN and > description. The nearest thing to me right now is a tub of pencils. > > EAN: 5011772007888 > Name: 50 Staedtler HB Pencils > Description: A tub of 50 HB Pencils by Staedtler. > > Suppose someone at BarcodeDB.com, having signed in, wanted to change the description to: A tub > of 50 HB Pencils by Staedtler. With strong break resistant leads, they make an ideal pencil for kids > or adults alike. > > Rather than update this description straight into the database. It would be nice for it to wait > to be approved, and once approved a 'history' is kept so it is known who submitted the > data and who approved it. Any ideas on how to achieve this effectively?? > > Thanks, > > Alex If you want to accept/reject an entire set of changes, you could have a pending_updates table with the same columns as your main table (plus a primary key to identify one update request from another for the same product, and to track the date/time/status of the change, etc.) Then when you approve the change, you can execute something like this (MySQL): UPDATE product, pending_updates SET product.name = pending_updates.name, product.description = pending_updates.description WHERE product.EAN = pending_updates.EAN AND pending_updates.sequence_no = ?; ...and then either delete the rows from pending_updates or set a processing date on them so you don't do the same update again. Andrew