Re: Table filter

Il Wednesday 21 November 2007 16:41:03 Rodrigo De León ha scritto:
> On Nov 21, 2007 9:21 AM, Reg Me Please <regmeplease(at)gmail(dot)com> wrote:
> > Hi all.
> >
> > I've the following concept.
>
> <snip>
>
> This smells like EAV.
>
> Please read
>
> http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-man
>y.html
>
> and consider reevaluating the schema according to valid relational
> design (tables, columns, check constraints, etc.).
>
> In any case, good luck.

You are right, Rodrigo.
It smells like EAV because it is.
But I need to take into account the history of changes for evey single
attribute of every single item.

And what's worst (for me), attributes can be changed for both future values
(that is values getting validity in the future) and past ("history gets
fixed"). A variant of the well known time-travel won't apply properly as I can
get single attributes changed.
Getting the proper attribute list for an item at a certain date it's a snap
in EAV, while it's a nightmare in the usual fields-in-a-table approach.

This is why I'm going EAV (+ timestamps and flags of course).

So the table actually is:

create table t_data
( property text,
  item int8,
  prop_value text,
flag bool,
valid timestamptz,
expires timestamptz,
);

And I hope that there's a better advise for such a scenario.

--
Reg me Please
<Non quietis maribus nauta>