data design question - record versioning

Wanted to get feedback from everyone. Current project requires the
need on certain key tables to keep versions of records, and to
possibly use those older versions to selectively rollback. The
business needs the ability to selectively rollback everything that
happened in the data load last Wednesday, for example, but keep all
data loads since then. So I'm exploring history/auditing tables,
filled by triggers, but it seems like a huge pain. I also saw an
article by Ben Allfree ( http://www.codeproject.com/useritem...ing.
asp
) on codeproject that was interesting, but was conceptually quite
different, with some detractors in the feedback. Surely this is a
common issue. What are everyone's thoughts?Common issue, no common answer. I'd say it is dependent on your application
(just like we DBA like to put it). The ratio of active and inactive records
plays an important role in here.
You may also consider set up (indexed) view to help address the issue.
Quentin
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
> Wanted to get feedback from everyone. Current project requires the
> need on certain key tables to keep versions of records, and to
> possibly use those older versions to selectively rollback. The
> business needs the ability to selectively rollback everything that
> happened in the data load last Wednesday, for example, but keep all
> data loads since then. So I'm exploring history/auditing tables,
> filled by triggers, but it seems like a huge pain. I also saw an
> article by Ben Allfree (
> http://www.codeproject.com/useritem...dVersioning.asp
> ) on codeproject that was interesting, but was conceptually quite
> different, with some detractors in the feedback. Surely this is a
> common issue. What are everyone's thoughts?
>|||Hmmmm - that's what I was afraid the answer would be. I was hoping
there was a magical, documented, easy to implement solution.
There will be one and only one active record at any given time. There
may be 1-n history records. But realistically I doubt there would be
less than 15 history records associated with any given record. And
probably < 10% of all records would have any history at all. Majority
would be fresh inserts.
On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
wrote:
> Common issue, no common answer. I'd say it is dependent on your applicati
on
> (just like we DBA like to put it). The ratio of active and inactive recor
ds
> plays an important role in here.
> You may also consider set up (indexed) view to help address the issue.
> Quentin
> "CoreyB" <unc27...@.yahoo.com> wrote in message
> news:1180029403.355004.23520@.q69g2000hsb.googlegroups.com...
>
>
> - Show quoted text -|||I'd go with the active/passive flag. Sounds at most the inactive records
would be as many as the active records, if not drastically less. Putting
adequate index (on the columns PK and that states "same group") will almost
eliminate any access contention problems due to this active/passive issue.
<unc27932@.yahoo.com> wrote in message
news:1180110598.547933.90790@.o5g2000hsb.googlegroups.com...
> Hmmmm - that's what I was afraid the answer would be. I was hoping
> there was a magical, documented, easy to implement solution.
> There will be one and only one active record at any given time. There
> may be 1-n history records. But realistically I doubt there would be
> less than 15 history records associated with any given record. And
> probably < 10% of all records would have any history at all. Majority
> would be fresh inserts.
> On May 25, 12:10 pm, "Quentin Ran" <remove_this_qr...@.yahoo.com>
> wrote:
>|||Quentin Ran wrote:
> I'd go with the active/passive flag. Sounds at most the inactive records
> would be as many as the active records, if not drastically less. Putting
> adequate index (on the columns PK and that states "same group") will almos
t
> eliminate any access contention problems due to this active/passive issue.
I would also consider having a separate history table instead of
active/inactive flag.
Since the rollover/rollback operations are expected to happen
significantly less often than active record access/update, keeping the
history in a separate table may make sense.
Y.S.

> <unc27932@.yahoo.com> wrote in message
> news:1180110598.547933.90790@.o5g2000hsb.googlegroups.com...
>