Page 1 of 1

php record backup idea -- need second opinion please.

Posted: Sat Aug 29, 2009 10:27 am
by dajohnson1s
Hello,

Recently I launched an application to build inter-departmental newsletters and have been fine tuning since. But I am starting as it is being used more and more, I am wondering about when things are 'accidentally deleted'...I don't have a lot of experience in this area (only have built 'toy' programs that aren't used for anything).

So I have a couple of ideas of how to go about this, but this is what I am hoping to do. I would like to Copy or move the data that is being deleted to another location. This location could be a file, or another table in the database. But ideally I am hoping to provide a way that the users can resurrect accidentally removed items.

I have thought about this for a while now, and I am running into a snag here (due to lack of experience). The person in charge of the newsletters wants a way to remove newsletters (say one is accidentally created for the wrong day), and removing the entire newsletter is the problem.

My tables are organized like so:

Code: Select all

 
newsLetter           newsLetterSections       articles
-----------          --------------------     ------------
newsLetterID         newsLetterID               articleID
newsLetterTitle      sectionID                    sectionID
                           sectionTitle                 articleTitle
                                                           articleBody
                                                            articleURL
 


Ideally, when the newsletter is deleted, it could copy numerous sections and numerous articles.

I feel I am getting a case of 'tunnel vision' here, and could use another apporach or insight.

Thanks in advance
Daniel

PS. if it seems like I am being over protective for the users sake, the people that use this system, I am supprised they can turn their own computers on.

Re: php record backup idea -- need second opinion please.

Posted: Sat Aug 29, 2009 10:35 am
by Darhazer
Add NewsletterStatus to the newsletters table and instead of deleting the newsletter, change it's status.
Filter listing of newsletter by status, and give only the administrator of the system the ability to see and change the status

Re: php record backup idea -- need second opinion please.

Posted: Sat Aug 29, 2009 11:08 am
by dajohnson1s
Darhazer,

Thanks, now the only issue is convincing them we should use 'groups' rather than everybody have full access.

Should I do the same for the articles and categories that are removed as well? Thinking yes, but I didn't want to assume anything.

Daniel

Re: php record backup idea -- need second opinion please.

Posted: Sat Aug 29, 2009 11:35 am
by califdon
That's also the route I would follow, but I have a couple of additional comments. Nothing ever replaces the need for real backups, which is very different from what we're discussing here. You should make sure that your system administrator has a solid backup plan (daily, weekly, whatever), or you should independently look into such a system. Also, what Darhazer has described will handle accidental deletes, which might be all you need, but in case there is also a need to recover earlier "versions" after somebody has mistakenly edited the data, then you need to think in terms of a much more complicated "journaling" system that saves every transaction to a completely different table, so that it will be possible to go back and "undo" any updates. The underlying concept we're discussing here is that data is valuable and, for the most part, should never be destroyed. Obviously, there are exceptions, but if you start from that concept, you will be less likely to make some of the mistakes that cause developers to tear their hair out later.

Re: php record backup idea -- need second opinion please.

Posted: Mon Aug 31, 2009 1:10 pm
by dajohnson1s
See, this is my problem. The server that we have, only has one (root) login...and virtually no administration. Last semester I had more time, so what needed to be done, I researched and tackled. Our IT department says "you are on your own" since we are using linux/php/mysql for our needs.

Really kind of blows, as a student worker, maybe I care too much.

Sorry for the rant, I will look into the suggestions outlined here. I was kind of thinking of the 'journaling method', but then I found something called cron jobs. So I will take a look at that. I figure, backing the the database everyday, then it will handle the 'accidental edits'. Beats writing everything to a file as a log.

Thanks again
Daniel

Re: php record backup idea -- need second opinion please.

Posted: Mon Aug 31, 2009 2:18 pm
by califdon
That certainly limits what you can do, but I'd still recommend that you think first in terms of what the "right" thing to do is, then if you have to settle for less than that, maybe you'll have to do that. At least you won't be learning bad habits for the future. Do what Darhazer suggested: put a status flag (or a "date deleted" field) in each record. In all your queries, include the field in your WHERE clause. To your users it will appear that the records have been deleted, but you won't need to run any cron jobs or actually delete any records. I'm assuming that you are developing PHP scripts that your users will use to access the data, rather than using phpMyAdmin or command line access to the database. You can create your own access privileges, independent of the server and your IT department, either by establishing MySQL accounts or even in your PHP scripts. I'd recommend creating at least one MySQL account, to be used in all your user scripts, and which could have limited privileges. Since you already have root access to the database, you can do this yourself, without any involvement of the IT staff. Check out http://www.devshed.com/c/a/MySQL/Managi ... -Accounts/ and http://www.ntchosting.com/mysql/create-user.html and http://dev.mysql.com/doc/refman/5.1/en/ ... ement.html