I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now.
After trying several different approaches, such as adjacency list and nested sets, it seems that using closure tables would work best for this, since it allows other attributes of the relationship between two entities to be saved with the relationship. In my case, this includes the date the relationship started (FromDate) and the date it ended (ThruDate).
I have searched for a worked example of closure tables with additional attributes like dates with no success so far.
Does anyone know of a worked example of this design? I've used several things by Bill Karwin to help get started (primarily his book "SQL Antipatterns"), but they don't go far enough.
Any advice, links, or hints will be appreciated.