mysql - deleting records or marking them as deleted?

[sw45acp]

I'm interested in knowing which is a better alternative when working with mysql. If a record needs to be deleted using a php script, is it better to actually delete the record itself or instead mark it as deleted, such as having an extra column where deleted = 1 and active = 0.

 

Advantages of not actually deleting the record means that if the deletion was accidental, its still there. However, a disadvantage is that more space is taken up.

 

Fully deleting a record leaves overhead in the table, and if the records have an index that is auto incremented, a number is wasted, not that it means very much.  But if records will be deleted frequently the overhead will get quite large, and can be annoying to have to optimize the table.

 

What do you guys think is the better alternative?

[Philip]

Depends on what it is going to be used for.

[sw45acp]

True, I think I will just use the php optimize table syntax after any deletion is made.

[waynew]

If that "deleted" data (i.e active=0) could be useful later on, just mark it as inactive. If it's data that won't be useful later on, just delete it.