To Delete A Database Record Or Mark As Deleted? - A Discussion
Yesterday I received an often asked question from a close friend of mine and someone who I very much respect. In his words:
My new company has an existing system that I am rewriting. The system
has a database that follows the philosophy of never really deleting a
record - just set a flag marking it deleted. What do you think of this?In general, I think it is a bad idea. I think it just creates work
for you in other areas. But I could be naive.I'm using "ruby on rails", which has this guideline of DRY (Don't
Repeat Yourself). Rails puts things that are traditionally in the
database into the object-relational mapping layer, such as rules on
referential integrity. I guess referential integrity is normally in 2
places: application code (business logic) and database meta data.
Anyway, database constraints are rarely used in rails.The system is a community site, with discussion boards and lots of
other shared content that is contributed by members. Members may
leave and can be deleted or disabled.I'm tempted to delete members if they should be deleted. I'm tempted
to delete content if it should be deleted. I'm tempted to leave
content from former members unless the content itself is
inappropriate, though I guess content that is missing an associated
member record would be sort of an orphan. (I'm just thinking out loud
here).Do you have any general recommendations?
I would hate to add to every single sql statement "when (deleted is
null or deleted=0)". And I think there could be other issues, like
unique key collisions with records that are marked 'deleted'.On a project I created a schema that had sort of a "weak"
relationship in it. This was the goal planning system. The goal table
had a username column. This username column could be used to find the
user in the user table. But if the user was deleted, the goals still
displayed just fine. I guess if a user joined later, reusing a
previously used username they would inherit some existing goals.
Actually this feature was motivation for the schema — I started with
a set of goals from a big spread sheet and I wanted them to exist and
I wanted to be able to add users and them take ownership of goals
already in the system.Finally, if I delete records, do you recommend saving the deleted
record in another table? Is that a common practice?
Loading ...