pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
EAV or not to EAV?
3+ messages / 3 participants
[nested] [flat]

* EAV or not to EAV?
@ 2007-11-22 08:48  Reg Me Please <regmeplease@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Reg Me Please @ 2007-11-22 08:48 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

Hi all.

I have to take into account the update history of every single
field into a number of tables.
Those updates can come to the future (next values) but also to the
past (history fix) and apply only to some fields, usually not to the
entire record.
Every lookup in those tables is always related to a timestamp that
normally is the current timestamp, but can also be a past timestamp.

I've come up with a "traditionally regretted" EAV design with the add of
timestamps for the history. And the related problems, some of which have
been solved by Joe Conways's crosstab contrib.

Is there a better idea than mine? I hope so.

-- 
Reg me Please
<Non quietis maribus nauta>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: EAV or not to EAV?
@ 2007-11-22 10:57  Oleg Bartunov <oleg@sai.msu.su>
  parent: Reg Me Please <regmeplease@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Oleg Bartunov @ 2007-11-22 10:57 UTC (permalink / raw)
  To: Reg Me Please <regmeplease@gmail.com>; +Cc: pgsql-general@postgresql.org

On Thu, 22 Nov 2007, Reg Me Please wrote:

> Hi all.
>
> I have to take into account the update history of every single
> field into a number of tables.
> Those updates can come to the future (next values) but also to the
> past (history fix) and apply only to some fields, usually not to the
> entire record.
> Every lookup in those tables is always related to a timestamp that
> normally is the current timestamp, but can also be a past timestamp.
>
> I've come up with a "traditionally regretted" EAV design with the add of
> timestamps for the history. And the related problems, some of which have
> been solved by Joe Conways's crosstab contrib.
>
> Is there a better idea than mine? I hope so.

We use contrib/hstore for this


 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: EAV or not to EAV?
@ 2007-11-25 18:01  Jeff Davis <pgsql@j-davis.com>
  parent: Reg Me Please <regmeplease@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Jeff Davis @ 2007-11-25 18:01 UTC (permalink / raw)
  To: Reg Me Please <regmeplease@gmail.com>; +Cc: pgsql-general@postgresql.org

On Thu, 2007-11-22 at 09:48 +0100, Reg Me Please wrote:
> I have to take into account the update history of every single
> field into a number of tables.
> Those updates can come to the future (next values) but also to the
> past (history fix) and apply only to some fields, usually not to the
> entire record.
> Every lookup in those tables is always related to a timestamp that
> normally is the current timestamp, but can also be a past timestamp.
> 
> I've come up with a "traditionally regretted" EAV design with the add of
> timestamps for the history. And the related problems, some of which have
> been solved by Joe Conways's crosstab contrib.

Here are two more ideas:

1) Vertically partition the tables so that each field you want to track
is in its own table, and then track the history of those individual
tables. Then join the tables on a key that doesn't change when you need
to see the whole table. This works well if you only have a few non-key
fields per table.

2) Store a normal history, but also store a bitmap of the fields that
change in each record. You might use partial indexes to be able to
quickly select only those records where a certain field has changed.

EAV will give you many problems down the line, and I don't think it will
solve anything for you.

Regards,
	Jeff Davis




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2007-11-25 18:01 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-22 08:48 EAV or not to EAV? Reg Me Please <regmeplease@gmail.com>
2007-11-22 10:57 ` Oleg Bartunov <oleg@sai.msu.su>
2007-11-25 18:01 ` Jeff Davis <pgsql@j-davis.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox