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