Temporal data storage

From: Gerhard Heift <ml-postgresql-20081012-3518(at)gheift(dot)de>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Temporal data storage
Date: 2010-04-05 16:28:18
Message-ID: 20100405162818.GA28333@gheift.kawo1.rwth-aachen.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I have person to group mapping in which I want to store the time, when this
mapping is valid. I can store the valid time in an array or in seperate rows.

in rows:

create table group_mapping (
group name not null,
person integer not null,
valid_since timestamptz not null,
valid_until timestamptz not null
);

insert into group_mapping values('test', 1, '2000-1-1', '2001-1-1');
insert into group_mapping values('test', 1, '2002-1-1', '2002-1-1');

or in an array:

create type period as (
since timestamptz,
"until" timestamptz
);

create table group_mapping_array (
group name not null,
person integer not null,
valid_time period[] not null
);

insert into group_mapping_array values('test', 1,
array[
('2000-1-1', '2001-1-1'),
('2002-1-1', '2003-1-1')
]::period[]
);

some advantages and disadvantages:

with rows:
+ a gist index already exists (must be modified)
+ can add other attributes to the valid time
- prevent overlapping is very complex
- binary operations like "and", "or" and "not" operates on multiple rows

with array:
+ overlapping can simply done with a constraint and a function
+ binary operations like "and", "or" and "not" are easy to implement
- a gist index must be written nearly from scratch
- adding other attributes is complex

Are there other (dis)advantages I have forgotten?
Which solution is better?
Are there any drawbacks by using arrays in the rows?

Thanks,
Gerhard

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2010-04-05 17:34:13 Re: How to fix set-valued function called in contextthat cannot accept a set in earlier versions
Previous Message Tim Landscheidt 2010-04-05 15:22:40 Re: Null vs. Empty String in Postgres 8.3.8