Обсуждение: Table filter

Hi all.

I've the following concept.

create table t_data
( property text,
  item int8,
  prop_value text
);

(I've cut some fields away needed only for data history).
The meaning is that an entity called by the value of "item" has a number
of properties called by "property" with value "prop_value".
So, for a single "item" there can be many different "property" each with its
own value.

create table t_filters
( filter text
);

create table t_filter_def
( filter text references t_filters,
  property text,
  prop_value l text
);

A filter is a list of property values needed to qualify an entity as
"good". An entity evaluates as good only when all property values in the
filter match the ones associated to an item in t_data.

What's missing to me is how to apply a filter to the t_data and get the list
of the items that evaluate good.

--
Reg me Please
<Non quietis maribus nauta>

On Nov 21, 2007 9:21 AM, Reg Me Please <regmeplease@gmail.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-many.html

and consider reevaluating the schema according to valid relational
design (tables, columns, check constraints, etc.).

In any case, good luck.

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@gmail.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>

Reg Me Please wrote:

> The meaning is that an entity called by the value of "item" has a number
> of properties called by "property" with value "prop_value".
> So, for a single "item" there can be many different "property" each with its
> own value.

> A filter is a list of property values needed to qualify an entity as
> "good". An entity evaluates as good only when all property values in the
> filter match the ones associated to an item in t_data.
>
> What's missing to me is how to apply a filter to the t_data and get the list
> of the items that evaluate good.

I haven't played with it myself, but it seems to me that you could do
this with an inner join of two crosstabs -- one on t_data and one on the
filters (i.e. you join on the columns of the filter crosstab to the
matching ones in the t_data crosstab).

Joe

Il Wednesday 21 November 2007 20:22:46 Joe Conway ha scritto:
> Reg Me Please wrote:
> > The meaning is that an entity called by the value of "item" has a number
> > of properties called by "property" with value "prop_value".
> > So, for a single "item" there can be many different "property" each with
> > its own value.
> >
> > A filter is a list of property values needed to qualify an entity as
> > "good". An entity evaluates as good only when all property values in the
> > filter match the ones associated to an item in t_data.
> >
> > What's missing to me is how to apply a filter to the t_data and get the
> > list of the items that evaluate good.
>
> I haven't played with it myself, but it seems to me that you could do
> this with an inner join of two crosstabs -- one on t_data and one on the
> filters (i.e. you join on the columns of the filter crosstab to the
> matching ones in the t_data crosstab).
>
> Joe

This is a good point.
I just need to avoid doing crosstabs over a very large t_data: I fear it'd
kill the application.

--
Reg me Please
<Non quietis maribus nauta>

Reg Me Please wrote:
> Il Wednesday 21 November 2007 20:22:46 Joe Conway ha scritto:
>> Reg Me Please wrote:
>>> The meaning is that an entity called by the value of "item" has a number
>>> of properties called by "property" with value "prop_value".
>>> So, for a single "item" there can be many different "property" each with
>>> its own value.
>>>
>>> A filter is a list of property values needed to qualify an entity as
>>> "good". An entity evaluates as good only when all property values in the
>>> filter match the ones associated to an item in t_data.
>>>
>>> What's missing to me is how to apply a filter to the t_data and get the
>>> list of the items that evaluate good.
>> I haven't played with it myself, but it seems to me that you could do
>> this with an inner join of two crosstabs -- one on t_data and one on the
>> filters (i.e. you join on the columns of the filter crosstab to the
>> matching ones in the t_data crosstab).
>>
>> Joe
>
> This is a good point.
> I just need to avoid doing crosstabs over a very large t_data: I fear it'd
> kill the application.
>

Yeah, I suspect as much too.

You might want to create a custom filter function based on the
crosstab_hash function in contrib/tablefunc. The basic elements are
there. Instead of building return tuples you could do the filtering and
return a boolean value for each row key or something like that.

Joe