Taoffi's blog

prisonniers du temps

Managing databases periodic membership (or: unique time span indexing) Part II

Ho to view periodic relationship data

In the previous post, I exposed a method for uniquely indexing time spans of periodic-memberships.


Here, we will discuss the next task: How to view historical data in such structures?

To illustrate this, we will talk about a portfolio sample data, simply because this is a more elaborate case (than the hotel/room reservation case).


Suppose we have:

§  A portfolio named ‘Sample portfolio’

§  A set of companies: company 1, company 2 and company 3

§  A set of daily historical data for each company for the period from 2010/01/01 to 2010/01/10


Now suppose that:

§  Company 1 and company 3 are ‘permanent members’ of the portfolio starting at 2010/01/01

§  Company 2 is a member of the portfolio for the following periods:

o   2010/01/01 to 2010/01/05

o   2010/01/08 to à endless


To query the daily historical data for company 2 related to its parent portfolio, we should thus obtain the data for the periods where it is effectively a member of the portfolio:

§  2010/01/01, 2010/01/02, 2010/01/03, 2010/01/04, 2010/01/05

§  2010/01/08, 2010/01/09 and 2010/01/10


(Note that data for 2010/01/06 and 2010/01/07 should be ignored)


This can be done using a view similar to the following (TSQL followed by the view design diagram)


SELECT     p.name AS portfolio,

                c.name    AS company,




FROM dbo.portfolio_companies AS pc INNER JOIN

                      dbo.portfolios AS p ON pc.portfolio_id = p.id INNER JOIN

                      dbo.company_data AS history

                ON pc.date_start <= history.date

                               AND ISNULL(pc.date_end, CONVERT(datetime, '9999/01/01')) >= history.date INNER JOIN

                      dbo.companies AS c ON history.company_id = c.id AND pc.company_id = c.id


As said above, company 2 is a portfolio member during the following periods:

§  2010/01/01 to 2010/01/05

§  2010/01/08 to à endless




With our sample history data, this view produces the following data for company 2 (8 entries):



Note: the history data table contains the following data for company 2 (10 entries):



Comments are closed