I've many tables where data must be "marked for deletion" although not erased, or toggle between released and hidden data.

Most intuitive method to handle these cases would be to give a column within the database deleted int(1) or public int(1). This boosts the concern of not failing to remember to specify WHERE deleted=0 for every single time that table has been utilized.

I considered conquering this by creating duplicate tables for erased/unregistered data for example article => article_deleted and moving the information rather than removing it. This gives with 2 issues:

  1. Foreign key constraints finish up being very annoying to keep
  2. Quantity of tables with hidden content doubles (during my situation ~20 becomes ~40 tables)

My last idea is to produce a duplicate from the entire database known as unreleased and migrate data there.

My question is not about safety from the data management, but much more of - what's the proper way to do it right from the start?

I've encounter this exact problem before and i believe it's a bad idea to produce an unnecessarily cumbersome DB since you fear so much bad code.

It will be a better idea to complete thorough testing in your Test server before you decide to release to production. Even I had been tripped up through the "Erased" column a couple of occasions initially when i first experienced it however i eventually caught on, and when you've got a proper Dev/Test/Production atmosphere you ought to be fine.

To sum up, keep your remove column and require more out of your programmers.

UPDATE:

Alternatively you can produce a view that only pulls the records that are not erased and make certain everybody uses that for choose queries.

I believe your initial approach is "correct" and "right", however your worry about it being slightly error-prone is really a valid one.

You'll most likely have to make certain that the test methods are rigourous enough to trap errors.

The very first approach is the greatest I have develop. I call the column active rather than erased. The record is available but it may be either active or inactive. That then if you absolutely have to remove things the terminology does not get screwy.

Saying "Remove the inactive records" is sensible but saying "Remove the erased records" just will get confusing.