Re: Deleted Flag/Unique Constraint

Yeah, personally, I'm not entire 100% convinced of the concept myself and am
actively investigating alternatives. Most of what I'm doing right now is
simple research, as well as trying out a few ideas to see what works in
practice and what doesn't. Unfortunately, this is one of those areas where
I have yet to find a lot of guidance on the issue.

Bryan

On 3/29/07, Lew <lew(at)nospam(dot)lewscanon(dot)com> wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>