LebGeeks

mir

Member

Delete Conversation

Hi, guys...
just want to share some programming thoughts with you..umm some geeky ones...isn't that the lebgeeks forum .. it is fine if u did not get this, found it stupid 

there is a type of users that DBAs and Programmers call Pain in the A**
that is the press-delete button user, and i want my data back (nag..shout..cry..more wining...and more na2 w headache)

so i was thinking that maybe after all we should not actually delete the data in some table, y not put a status flag
my relational database table would be like :
code(numeric)  name(nvarchar[50]) stat (bit)

of course u guessed it, i will use the 1 and 0 to signal if the record is visible yes or no...but of course that will work only with small record counting tables like the employees or holidays   table and it is somehow a hastle in programing as every select must have an additional condition

for the big and critical tables like money_transaction that won't really work another solution  is to do a history_tblname table and move the records instead of deleting
that will increase the number of lines in ur code and u will do 2 transactions instead of one :( , but still somehow acceptable i think
this got me thinking if i can add special SQL like MIRDEL from tb1 to tb2 where condition  , this will do those 2 transactions in 1 ,is there anything similar to the MIRDEL that can do that...anyone knows ?

I know that some of you might be thinking, but hey do a daily backup and u are done... well that can be easily done of course, but in some cases it won't work as some user can really f*** up a database in less than a min, and backup becomes not that useful and of course u hear the complain about re-doing the work, and in some systems there are transactions in one table that u need to Virtually delete and backup the whole complete database is a waste of storage and takes time, and have u ever heard about the wacked weird wanted reports that must contain data previous,current and future data  (yes i did report that would predict accounting numbers after 2 years according to the current rythm    :(  [...was a pain]

So what u think of my Virtual Delete?
anyone had this idea before ? how do u as a programmer judge it?
do u think this can be done in real life systems ? would u dare to do it  as programmer ? anyone knows any sql command that moves a special record from a table without using insert/delete
w heik, just share ur delete thoughts ....

HEHEHE.. oops that was long weird post ..

Last edited by mir (January 12 2006)

Dark_angel

Banned

Re: Delete Conversation

Go away and come back when you have developed some better database programming skills!

did I mention that your english syntax sucks?!

Last edited by Dark_angel (January 12 2006)

mir

Member

Re: Delete Conversation

umm...that was geeky personal thoughts....
okey.. i will stick to the delete from tblname where condition (lying  )

and have the current problems with foreign keys constraints... oh! of course i won't forget ON Delete Collapse ... eh!
and the " i want my data back" problem

ano my goal is to have an open minded conv about solution to those problems

has any client came to you asking for a list of the employees that worked in the company and oh! don't forget that he pressed the delete button after he hired that employee

anyway, i don't think u can judge my programming skills been working in programming company (yes a job!serious stuff) since 2nd year in college
and actually i drove my modeling teacher crazy once because of a solution i offered to a certain problem.. he said it was nonsense and impossible, and guess what! i did the same concept at work, it saved us lot of pain and working so fine.
we implemented it

about my english... i will try to improve.. but that is not harvard litterature forum i think...anyway appologies for my english

mahdoum

Member

Re: Delete Conversation

I personally did a small database system using pascal as part of a school project, I used the grandfather-father-son technique, whereby you have a table where transactions are held, master file and old master file. When you merge the masterfile and transaction file, you back up the masterfile first. I also had a deleted table, whereby all deleted data was stored in that table.

rolf

Member

Re: Delete Conversation

I think moving anything to another table is a bad idea, it'll complicate things substancially.

I think it's a good idea to have "hidden" records in the SAME table, then you can regularly run a job that will really delete the "hidden" records that are older than, say, 3 days.
You can also have a function that will make hidden records (hidden during the last hour, day, etc...) reappear.
You'll have to apply this to the whole DB (all the tables) if you dont want to break relationships.
It also doesnt protect users from MODIFIYING database records.

You need a way to know WHEN the record was deleted, so that you can weed out old records, or have the possibility for example to restore only records deleted during the last hour. I propose that, instead of writing 1 or 0 in your special field, you write a timestamp of the time at wich the record was "deleted" (=hidden), or 0 if it is visible (not deleted)

The advantage that this system has over database backup is better control of how far to go back, you can for example restore what was deleted during the last 10 minutes only. Another advantage is that it will not affect the record modifications that have been done, which can also be a disadvantage, because some users will modify data then say "oooops!"

Last edited by rolf (January 13 2006)

mir

Member

Re: Delete Conversation

Thank you rolf for sharing your idea about putting a date and 0 field.. that will allow a greater control of the database, u are absolutely right..

I think that i might actually use this in tables that do not contain lot of transactions
and rut a utility that will clean or back up old old records.

And i Believe the best solution is to combine the transfer data technique and the hide technique...
I am still trying to find out a new way...
mahdoum suggestion is not also bad  ...

thank you guys for ur ideas, after all sharing :shy some self ideas helped improve them.

and i think that is very useful in case of legal issues like contracts, official papers...

If something else comes to my mind i will keep u updated

KingRhye

Member

Re: Delete Conversation

You probably don't wanna hear THIS, but you're only complicating things and adding overhead to your database, thus reducing its performance.
The best solution is to have a good daily maintenance plan, including full database backup !!
Now if you're into S&M, that's a different thing. I would then suggest you don't grant access to tables except by means of "views" which will not reveal the timestamps or whatever you wanna add. Then you could use "INSTEAD OF" triggers to implement this whole thing automatically. If you don't know much about views and triggers, then my friend you're a newbie and you need to put those basic sql statements behind you and learn some advanced stuff. I suggest you stop wasting your time with MySql, at least until they implement all the really useful things that exist in Microsoft SQL server.. etc etc.. I'm bored.

mir

Member

Re: Delete Conversation

You probably don't wanna hear THIS

Glad u posted, very useful post...

what i suggested might be used in some cases,with some tables,it may not be the perfect solution for all the databases

I am not saying that the backup database is a bad thing
Daily maintenance plans can be on a hour basis, not only on Daily basis.
i think u will then restore data in some other database and have ur program read from multiple databases,that is an idea! or u would write a DTS package
of course i will get an extra hard disk

Of course the use of Views and Triggers is very useful!
I had some theorical experience with them.
did very small actual work on them,but hey i am still learning

KingRhye

Member

Re: Delete Conversation

If you decide to use a separate database to store the changes in the original database, then this is similar to full auditing and can be implemented by using "ON INSERT", "ON UPDATE" and "ON DELETE" triggers, not "INSTEAD OF.." triggers. This approach is way better than the one you mentioned earlier, but then again, you'll have two databases and you'll need to script a convenient recovery method.
Still, I stress on implementing a good maintenance plan. The one I use mostly consists of two backup jobs (in addition to other tasks): one daily full backup, and one hourly differential backup. It solves most your problems and saves disk space.

rolf

Member

Re: Delete Conversation

I suggest you stop wasting your time with MySql, at least until they implement all the really useful things that exist in Microsoft SQL server.. etc etc.. I'm bored.

What about Postgre SQL?

KingRhye

Member

Re: Delete Conversation

I honestly haven't tried Postgre SQL and I'm not familiar with its capabilities. I only worked with Microsoft SQL Server 2000 and MySql so far. Would you recommend Postgre SQL as an alternative to MS SQL Server ?

rolf

Member

Re: Delete Conversation

I honestly haven't tried Postgre SQL and I'm not familiar with its capabilities. I only worked with Microsoft SQL Server 2000 and MySql so far. Would you recommend Postgre SQL as an alternative to MS SQL Server ?

I have heard people recommending postgre sql over mysql, it is supposedly better for big traffic, but I havent' tried it

mir

Member

Re: Delete Conversation

Thanks for ur advice KingRhye!
ur combination works.I think it is really good
a very easy and clean combination

I actually use MS Sql server?
I know lot of stuff..but it is very big to master all of it.
so planning to master MS Sql server in a year or so [it is really huge]
Do u have the MS Sql Server 2005 version ?
can u write ur feedback about it ?

KingRhye

Member

Re: Delete Conversation

Sadly I haven't had time to check out new updates since MS SQL Server 2000. But soon I'll check its new features and try to get it to try it out, and I'll give you my feedback on it. Maybe someone else has worked on it and can give us good feedback ?