mishaluba (12/14/2010)
Thank you for the responses, but I was thinking more along the lines of slowly-chaning dimension, rather then audit/logging. In one of the earlier comments Jeff Storm showed a portion of his solution, which is closer to what I am after. I would like for the application to be able to "look" at the same data using different hierarchy structures (Hierarchy A, which existed at time X and Hierarchy B, which existed at time Y) and all of them have to be equally accessible in the same set of tables.Thank you!
I actually just got done building this exact solution yesterday. I have a parent child relationship in our customer (accounts) table, and it's using SCD. I wish I had time to explain how I solved this. ...
It's going to have to wait until I can make a blog on it. Maybe I can do that this weekend. Right now I don't have time to explain it. 🙁
I'll try and post back here if I can blog on it this weekend...
-------------------------------------------------------------------------------------------------
My SQL Server Blog