2

I have many tables where data needs to be "marked for deletion" but not deleted, or toggle between published and hidden data.

Most intuitive way to handle these cases is to add a column in the database deleted int(1) or public int(1). This raises the concern of not forgetting to specify WHERE deleted=0 for each and every time that table is being accessed.

I considered overcoming this by creating duplicate tables for deleted/unpublished data such as article => article_deleted and moving the data instead of deleting it. This provides with 2 issues:

  1. Foreign key constraints end up being extremely annoying to maintain
  2. Number of tables with hidden content doubles (in my case ~20 becomes ~40 tables)

My last idea is to create a duplicate of the entire database called unreleased and migrate data there.

My question isn't about safety of the data management, but more of - what is the right way of doing it from the beginning?

Community
  • 1
  • 1
Mikhail
  • 8,379
  • 6
  • 50
  • 79
  • That's extra pain you are taking for very common problem. Having a extra column `deleted` is much more simpler. I recommend against duplicate table. – Nishant Mar 08 '11 at 18:13
  • I'd be appreciative to MySQL having a built-in column among the lines of `DNR_ON_SELECT` (do not return), and if such column exists and is set to 1, then an explicit `SELECT IGNORE_DNR ...` is needed – Mikhail Mar 08 '11 at 18:17
  • A minor suggestion, but I'd have thought it would be slightly more logical to have an `active` field rather than `deleted` one. (Perhaps it's just me, but it feels more natural to automatically include a `WHERE ... active=1` clause.) – John Parker Mar 08 '11 at 18:21
  • Similar to what @kevin-gale said. I have a fundamental issue with the concept. I should select all information from the needed table. not "all except that that isn't active" – Mikhail Mar 08 '11 at 18:24
  • Yeah it's less than ideal. You can create a view to use when selecting the data but that gets confusing as well. It's a good question but I'm not sure there is a good answer. – Kevin Gale Mar 08 '11 at 18:37

3 Answers3

3

I have run into this exact issue before and I think it is a bad idea to create an unnecessarily cumbersome DB because you are afraid of bad code.

I think it would be a better idea to do thorough testing on your Test server before you release to production. Even I was tripped up by the "Deleted" column a few times when I first encountered it but I eventually caught on, and if you have a proper Dev/Test/Production environment you should be fine.

In summary, keep the delete column and demand more from your coders.

UPDATE:

Alternatively you could create a view that only pulls the records that aren't deleted and make sure everyone uses that for select queries.

Abe Miessler
  • 79,479
  • 96
  • 291
  • 470
  • 1
    Upvote & accept for creating a View. This can be extended to where tables that are allowed to be used on productions have to begin with a certain character. This allows to have "deleted" or "inactive" or "staff_only" flags, while fixing all `SELECT` in just 1 modification of the view – Mikhail Mar 08 '11 at 18:35
  • +1 for "create a view". Actually, it's pretty brain-dead not to have two views, one for "active" and one for "inactive" articles, and triggers (if necessary) to let you update the views. Then you can just use the views instead of the base tables. – Mike Sherrill 'Cat Recall' Mar 10 '11 at 11:35
0

I think your initial approach is "correct" and "right", but your concern about it being slightly error-prone is a valid one.

You'll probably just have to make sure that your test procedures are rigourous enough to catch errors.

Lightness Races in Orbit
  • 369,052
  • 73
  • 620
  • 1,021
0

The first approach is the best I've come up with. I call the column active instead of deleted. The record exists but it can be either active or inactive. That then if you really do need to delete things the terminology doesn't get screwy.

Saying "Delete the inactive records" makes sense but saying "Delete the deleted records" just gets confusing.

Kevin Gale
  • 4,160
  • 6
  • 29
  • 31