Taoffi's blog

prisonniers du temps

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

Introduction

The relations between some ‘real world’ objects can sometimes be in the form of ‘periodic membership’. By ‘periodic membership’, I mean the context when one object can be member of another during one or more periods of time.

Examples:

·         A person who occupies a hotel room during a period of time;

·         A company which is member of a portfolio during a period of time;

·         … etc.

 

The context also assumes that we want to keep the membership history and be able to build data aggregations that take into account these periodic memberships. (For example: be able to calculate portfolio performance during a period of time that spans several ‘periodic memberships’).

 

A concrete example may better explain the case:

§  We have a portfolio for which we want to maintain historical performance during time;

§  At 2009/01/01, the portfolio contained : company1, company2 and company3;

§  At 2009/03/01, we changed the portfolio components and excluded company2. (Now the portfolio contains only company1 and company3);

§  At 2009/06/01, to calculate the portfolio performance for the period 2009/01/01 to 2009/06/01, we should take company2 into account for the period 2009/01/01 to 2009/03/01 (although it is no more part of the portfolio).

 

Knowing that database engines do not offer a ‘time-overlap’ unique indexing features, resolving this situation needs a structure that allows us to assign and manage periodic membership indexes.

Note: I am surely not the first to confront this context, but I wanted to share my experience to resolve this problem and will be glad to receive any thoughts about it.

 

Periodic membership multiplicity models

In my experience, I encountered two possible types of multiplicities related to periodic relationship:

§  One-to-one periodic membership: a good example for this would be a hotel room that can be occupied only by one person during the same period (i.e. period is unique per room);

§  One-to-many periodic membership: a good example for this would be a portfolio which can have more than one member for the same period (i.e. period is unique per portfolio-member).

In this article, I will explain how to handle both of these types.

 

Hotel room reservation, the basic structure

To demonstrate a hotel reservation periodic membership (uniqueness of room/reservation period), we can create the following table and relationships:

 

§  Hotel clients: contains client list items

§  Hotel rooms: contains room list

§  Hotel room reservations: contains client/room reservation periods

 

Note: the date_end field of the reservations table can be null, to allow ‘permanent’ (or endless) reservations.

 

To ensure uniqueness of reservation periods, we will create a scalar function (returning a Boolean value) that can be used as Check Constraint on the hotel_room_reservations.

 

CREATE FUNCTION [dbo].[is_valid_hotel_reservation_period]

(

       @item_id     int,

       @room_id     int,

       @date_start  datetime,

       @date_end    datetime

)

RETURNS bit

AS

BEGIN

       -- validate the provided parameters    

       if @date_start is null or @room_id is null

             return 0

 

       -- be sure start date < end date 

       if not @date_end is null

       begin

             if @date_start >= @date_end

                    return 0

       end

      

       -- is this the first reservation period for this room?

       if ( select count(*)

                    from dbo.hotel_room_reservations

                    where( room_id = @room_id) ) <= 0

             return 1

      

       -- check overlapped periods

       if @date_end is null              -- is this a permanent (endless) reservattion?

       begin

             if ( select  count(*)

                    from dbo.hotel_room_reservations

                    where( ( room_id = @room_id)

                                  and( id != @item_id)

                                  and(( date_end is null) or (date_end >= @date_start))

                           )) > 0

                    return 0

       end

       else

       begin

             if ( select  count(*)

                    from dbo.hotel_room_reservations

                    where(       (room_id = @room_id)

                                  and( id != @item_id)

                                  and( date_start <= @date_end)

                                  and(( date_end is null) or (date_end >= @date_start))

                           )) > 0

                    return 0

       end

      

 

       -- return OK

       return 1

END

 

We can now use this function as check constraint on the reservation table:

 

 

Overlapping entries in the reservation table would now display an error message like:

The Portfolio/company periodic membership case

In portfolio/company case, membership should allow multiple membership instances for different periods. That is: the same company can be member of the same portfolio several times as long as the membership periods do NOT overlap.

 

Our tables and relationships may look like the following:

 

All what we need is to create a new version of the above function, and use it as a check constraint condition for the portfolio companies table:

 

CREATE FUNCTION [dbo].[is_valid_portfolio_company_period]

(

       @item_id     int,

       @portfolio_id int,

       @company_id  int,

       @date_start  datetime,

       @date_end    datetime

)

RETURNS bit

AS

BEGIN

       -- validate the provided parameters    

       if @company_id is null or @date_start is null or @portfolio_id is null

             return 0

 

       -- be sure start date < end date 

       if not @date_end is null

       begin

             if @date_start >= @date_end

                    return 0

       end

      

       -- is this the first membership period for the portfolio/company?

       if ( select count(*)

                    from dbo.portfolio_companies

                    where( portfolio_id = @portfolio_id)

                           and( company_id = @company_id) ) <= 0

             return 1

      

       -- check overlapped periods

       if @date_end is null              -- is this a permanent (endless) membership?

       begin

             if ( select  count(*)

                    from dbo.portfolio_companies

                    where( company_id = @company_id

                           and( portfolio_id = @portfolio_id)

                           and( id != @item_id)

                           and(( date_end is null) or (date_end >= @date_start))

                           )) > 0

                    return 0

       end

       else

       begin

             if ( select  count(*)

                    from dbo.portfolio_companies

                    where( company_id = @company_id

                           and( portfolio_id = @portfolio_id)

                           and( id != @item_id)

                           and( date_start <= @date_end)

                           and(( date_end is null) or (date_end >= @date_start))

                           )) > 0

                    return 0

       end

      

       return 1

END

 

We can, now, use the function as a check constraint condition for the membership table:

 

 

Possible extensions

It seems that, with some more efforts, this can evolve to a generic Time-Span unique indexer.

Waiting to hear some thoughts about thisJ!

Comments are closed