Database design for versioned data

We're working on an app that needs to keep versioned data (i.e., the
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
----
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.