Temporal data storage

Поиск
Список
Период
Сортировка
От Gerhard Heift
Тема Temporal data storage
Дата
Msg-id 20100405162818.GA28333@gheift.kawo1.rwth-aachen.de
обсуждение исходный текст
Ответы Re: Temporal data storage  (Jeff Davis <pgsql@j-davis.com>)
Список 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

Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Tim Landscheidt
Дата:
Сообщение: Re: Null vs. Empty String in Postgres 8.3.8
Следующее
От: Alban Hertroys
Дата:
Сообщение: Re: How to fix set-valued function called in contextthat cannot accept a set in earlier versions