Re: Deleted Flag/Unique Constraint

Bryan Murphy wrote:
> I think the other guys suggestion will work better. ;)
>
> Really, the table was just an example off the top of my head. I believe
> we do use a boolean as the deleted flag. We primarily use it to track
> the user who originally created an item (even if their account is
> deleted). It's a bit like a financial system where you can't
> retroactively change the data. We always want to know who was associated
> with the original transaction, even long after their account was deleted.
> Thanks for the suggestion though!

(Your post would have been clearer if it were inline with the material quoted,
rather than top-posted.)

Your requirements analysis raises an important point, one that I've seen
misused in practice. In your data model, "deleted" is a business concept -
the example "deleted account" is an account that is NOT deleted in the
database, but exists with a business attribute "deleted".

I've seen that sort of flag used absent any business requirement to maintain
current information about a "deleted" fact, but instead as meta-data to audit
the database usage. The bad effect was that all business logic had to account
for the "deleted" flag even though it had no semantic in the business domain.

I speculate that a separate meta-data table is more apt for such a use,
although I continue to investigate scenarios where it makes sense to keep
historic facts in the same table with current facts. Naturally this opens up
the world of temporal databases.

My hypothesis is that the business-domain semantics of the facts that a table
models must be temporal in order to keep historic facts therein, If the
purpose for the history is not rooted in the business domain, then such facts
must be in separate tables from those that model the business domain. So a
"deleted" account attribute merits a column in the "accounts" table, but
record deletion facts just to monitor database usage should be separate. Both
might make sense in the same implementation, yielding "accounts" and
"accountsaudit" tables.

I am not yet convinced that I have the answers on this matter.

-- Lew