[MickySmig]

Hi,

I'm currently building a database and one of the requirements is that the users of the application should not be able to delete any records. My nitial idea was to include a bit field in each table that marks the record as deleted and then filter it out with a WHERE clause in the select statement, giving the user the appearance that the record has been deleted.

My problem comes when I want to cascade a delete. Normally, this is done by the RDBMS, but i was wondering if anyone had any ideas as to how I could include this functionality in the scenario described above.

Any help or advice on this would be greatly appreciated.

Best regards,

Mick

[blindman]

Please explain why you need to cascade deletes when you are not allowing deletes.

[MickySmig]

Hi,

Sorry for the confusion. I want to be able to mark the child records as deleted using the same method of using a flag.

For example, when the flag in the parent record is checked, I want to be able to check the flag on all related child records. So, to the user, it appears that the child records have been deleted as well.

To be honest, I'm not that good with databases, so this might not be possible, but if anyone has dealt with this problem before, I would appreciate any advice they could give.

Mick

[blindman]

You should just check the parent record to determine whether the child record is deleted. Create a view to do this, and for added efficiency make it an indexed view.

[MickySmig]

Thanks blindman,

The problem is that I'm trying to incorporate a way of restoring records that have been deleted by mistake. I've noticed that there are tools such as Apex SQL Log and SQL Log Recovery that claim to be able to do this, but I'm relatively new to database design and administration (I've just left university) and I don't know if these applications work as described.

Would you or anyone else on the forum have any ideas as to the best way to recover records that have been changed or deleted in error. It was my first thought to include a deleted flag, but now I think about it, you'll probably know of better ways of achieving what I want.

Again, thanks very much for the help.

Best regards,

Mick





Mick

[blindman]

How often do you expect to need to be able to restore deleted records? Are you going to give the users this ability?

[MickySmig]

Hi blindman,

The application is a student management system with teachers, students, a registrar, and our department acting as administrator. When I initially made this post they wanted only the administrator to be able to actually delete from the database, but include delete buttons that give the user the appearance that they have deleted (if that makes sense). I've now discussed this with them and they've changed their mind and said that the teachers should not be presented with any delete functionality at all (basically just giving them the ability to insert, read, and update) and the Registrar should be the only role with the ability to delete.

The system is being built using ASP.NET 2.0 and SQL Server 2005, so I was thinking that I could just show or hide the delete buttons based upon these roles. This would still leave the problem of the registrar mistakingly deleting records, but, at least. only one role can make such errors.

Best regards,

Mick

[blindman]

Requirements, requirements, requirements....

[mike_bike_kite]

Originally Posted by MickySmig

I'm currently building a database and one of the requirements is that the users of the application should not be able to delete any records. My nitial idea was to include a bit field in each table that marks the record as deleted and then filter it out with a WHERE clause in the select statement, giving the user the appearance that the record has been deleted.

My problem comes when I want to cascade a delete. Normally, this is done by the RDBMS, but i was wondering if anyone had any ideas as to how I could include this functionality in the scenario described above.

Any help or advice on this would be greatly appreciated.

I would just use from_date, to_date and upd_user on each table in your database. The from_date is when the data was written, the to_date will be '31 dec 9999' for the current record and in other records would just store the time they were "deleted".

To view data you would only show records where to_date is '31 dec 9999'. Then in your case I would delete records with a stored procedure for each table. It would set the to_date for the current record to the current time. It would then also step through each "cascade table" and set the to_dates on the corresponding records in those tables. You can use these dates to un-delete records or rollback changes in general.

This is roughly what was used in this little system where you can see the changes that have been made by selecting the history link and, if you had permission, you could rollback all the changes made by a user (or a feed or all feeds) to any point in time.

Mike

[georgev]

Mike, what's wrong with using a NULL to_date?

[mike_bike_kite]

Originally Posted by georgev

Mike, what's wrong with using a NULL to_date?

The to_date is obviously used to get the latest record. Let’s assume you have a history of 10 records for each live record. You normally want to get to the latest records as quickly as you can so the to_date would be made part of the index. I guess you might have something like id & to_date as the index but it depends on how you want to access the data.

If you use NULL to mark the latest record then most RDBMs will just ignore that field when doing an index look up. That means every time you access an id you’ll be pulling back all it’s history data too and then sieving through these to find the record with a NULL to_date. If you have a fixed value i.e. ’31 Dec 9999’ to mark the current record then this will be used by the index and you’ll just get the one current record back when you want to get the latest record.

Quite often people suggest using just one field (from_date) rather than two date fields. This obviously has the advantage of being easier to process when making changes to the record i.e. you don’t have to set the old current record’s to_date to the current time before adding the latest record though it will be slower to access the latest record as you need to find the maximum from_date and then pull the data with that from date. The big minus is how do you store that the record has now been deleted?

Mike

[pootle flump]

Originally Posted by mike_bike_kite

If you use NULL to mark the latest record then most RDBMs will just ignore that field when doing an index look up.

Not the ones I use. Which ones ignore NULL?

Originally Posted by mike_bike_kite

Quite often the “experts” on this forum suggest....

I stopped reading at this point - obviously you haven't started a little flame war for a while and are growing restless.

[georgev]

Using a single from_date wouldn't give you all the information needed - how would you know if it was current or not? - so I don't see this one being suggested.

A single to_date is possible, but this would all depend on

Originally Posted by blindman

Requirements, requirements, requirements....

I will never claim to be an expert on indexes (or should that be indices?), but an index ignoring NULL values? There's a flaw in that logic that I don't think the vendors would have overlooked.

There is a whole other set of questions that could come off the back of this about how NULLs are treated, but that's a separate topic.

[mike_bike_kite]

I altered my post to make it more friendly (my excuse is it’s Monday) and to get you to read he point. I assume the index on id & to_date will be the primary key i.e. you only want one current record for any id.

Let’s say I want to access the record that was current on ‘4 july 2008’ I’d have to write my query in the following ways :

-- using to_date of '31 dec 9999'
select * from MyTab 
where    id=@id 
         and @date >= from_date 
         and @date  < to_date

-- using NULL to_date
select * from MyTab 
where    id=@id 
         and @date >= from_date
         and @date < isnull( to_date,date, ’31 dec 9999’ )

select * from MyTab 
where    id=@id 
         and ( @date >= from_date 
                 and @date < to_date
               or 
                 @date >= from_date 
                 and to_date is null )

Which example do you think will be faster? Remember that only Oracle allows you to have an index on a function i.e. isnull() so sybase would certainly not use the to_date in example 2 and then you have to deal with the OR in example 3.

As a side point I’m not sure all RDBMs will treat a NULL as a unique value – i.e. could you inser 2 records with the same id and null for the to_date? It depends on whether your system treats the NULL as a unique value or as simply unknown. Sybase stops you from doing this but I'm unsure how other systems behave. A fixed maximum date will certainly remove this issue.

The SQL code is also more complex with NULL to_dates which I’d say is reason enough to not use it.

I'll admit it will cause a Y2K problem at some point in the future (31 dec 9999) but I don't think I'll be around to worry. If anybody feels their system will still be in use at this point then perhaps you should use NULLs.

Mike

[MickySmig]

Hi Guys,

Many thanks to all of you for the help. I'll definitely have a look at the examples that use two dates, but I won't be putting it into the system until the next version, as I'm still a newbie and don't want to bit off more than I can chew with this first attempt.

Many thanks again,

Mick