DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:Mime-Version:In-Reply-To:X-Apple-Yahoo-Original-Message-Folder:Content-Type:Content-Transfer-Encoding:Message-Id:Cc:X-Mailer:From:X-Apple-Yahoo-Replied-Msgid:Subject:Date:To;
	b=Iumy3oHwce6is1DDh9zIBL0Lkops8qr3T2SfSTn6filtN1GkJRXfMHX5oF4p2+9Xuh8Zt5EKv7/Bqkk2D4qC/vxAiFCIBYekKmyvjETrvJbFyCGA4u1iGNh2RGrKMrk2JjabgzjYTL+JhjjwIlBrfiDje2PFaYrXu2PbjE6pxPU=
	;
References: <4EA29DDB.9000300@linos.es>
	<00655F1E-08DC-42C5-B702-C8D332081557@yahoo.com>
	<4EA2E3A8.8090203@linos.es>
Mime-Version: 1.0 (iPad Mail 8J2)
In-Reply-To: <4EA2E3A8.8090203@linos.es>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-Id: <8B9FEA8A-D98D-4B5C-B957-2949A05DF9E6@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: advice on how to store variable attributes
Date: Sat, 22 Oct 2011 12:10:48 -0400
To: Linos <info@linos.es>

On Oct 22, 2011, at 11:39, Linos <info@linos.es> wrote:

> El 22/10/11 14:53, David Johnston escribi=C3=B3:
>> On Oct 22, 2011, at 6:41, Linos <info@linos.es> wrote:
>>=20
>>> Hi all,
>>>   i need a little of advice on what could be the best way to store this i=
nformation.
>>>=20
>>> We need to calculate the difference in costs for our operations, we are a=
lready
>>> storing our vendor invoices in the database so calculate the monetary ch=
ange it
>>> is a no-brainer but we need to store special attributes for any of the i=
nvoices
>>> that we need to compare too, for example:
>>>   -electric provider: total Kw.
>>>   -water provider: total m3.
>>>   -car maintenance: kilometers of the car.
>>>   -mobile phones provider: international call minutes, national minutes,=
 number
>>> of sms, etc..
>>>=20
>>> And much more types/variables, the number of variables can change, not e=
very day
>>> but still can change, i would like that they can be defined/changed from=
 our
>>> application, so alter table to add columns don't seem the best way (stil=
l an
>>> option though). We will have "generic" reports that will show us changes=
 in
>>> costs and specific reports for the types with "extended attributes" that=
 we want
>>> to compare.
>>>=20
>>> To compare values from this "extended attributes" i think we have two wa=
ys:
>>>   1- have them in columns and use standard SQL.
>>>   2- create the columns with a function that reads this attrs and create=
 the columns.
>>>=20
>>> So far i thin we have this options:
>>>   1- a bunch of columns that would be null except when the type of the i=
nvoice
>>> uses them.
>>>   2- a table related with the vendor invoices table for every type of in=
voice
>>> with his specifics columns.
>>>   3- a key/value in a separate table related with the vendor invoices ta=
ble where
>>> i store the extended attrs of every invoice that needs them.
>>>   4- use a hstore column in the vendor invoces table to store this attrs=
.
>>>=20
>>> The first two have the problem of probably changes to the number of attr=
ibutes
>>> of every type and give a more closed solution, apart from that 1- seems t=
o be a
>>> bit awkward and 2- would need the application that creates the query to k=
now
>>> with what table should join for every type (other point we will need to c=
hange
>>> if we want to create new invoices types).
>>>=20
>>> The last two have his own problems too, with 3 i will need to create a f=
unction
>>> that return rows as columns to compare them, with 4- given that i will s=
tore the
>>> attrs of every type in the database anyway i can use the operator -> (wi=
th a
>>> CASE using operator ? returning 0 if the searched attr it is not in the h=
store)
>>> but still don't seem a clean solution for me.
>>>=20
>>> For me it seems i am missing something, probably any of you have a much m=
ore
>>> elegant (or correct) way to handle this situation, what would be your ad=
vice?
>>> Thanks.
>>>=20
>>>=20
>>=20
>> Create a table with a single numeric column and multiple category columns=
.
>>=20
>> ( amount_value, amount_unit, amount_category, vendor_id )
>>=20
>> If necessary each "amount_value" data type should have it's own table sin=
ce the processing logic will vary (I.e., you cannot subtract text or Boolean=
 values).
>>=20
>> You are , in effect, creating multiple tables but combining them into one=
 and using the category column to distinguish between them.
>>=20
>> David J.
>=20
> Yeah, thanks for the advice David, if i understand you. this is (much bett=
er
> explained) my option 3, i have used this format any times for configuratio=
n
> tables with great success.
>=20
> I am not speaking about much data, maybe 200~300 invoices every month so t=
his
> should not be a problem in a long long time, the problem i see with this w=
ay of
> doing things it is how should i in the report queries get this values as c=
olumns
> to compare change in time? something like this?
>=20
> SELECT inv.invoice_id,
>              inv.total,
>          (SELECT amount_value
>               FROM vendor_invoices_attrs
>               WHERE invoice_id  =3D inv.invoice_id
>                  AND amount_category =3D 'international call minutes'),
>             (SELECT amount_value
>               FROM vendor_invoices_attrs
>               WHERE invoice_id  =3D inv.invoice_id
>                  AND amount_category =3D 'national call minutes')
> FROM vendor_invoices AS inv
>=20
> With hstore i think that could be:
> SELECT inv.invoice_id,
>              inv.total,
>              inv.store -> 'international call minutes',
>              inv.store -> 'national call minutes'
> FROM vendor_invoices
>=20
> For me the last option seems preferable but maybe i am missing a better wa=
y to
> get the first result or an added problem of hstore (other than datatypes s=
tored
> that i think i could check for validity in application or database regardl=
ess of
> not been integrated in hstore).
>=20
> Regards,
> Miguel Angel.


SELECT ...
FROM vendor_invoices
JOIN ( SELECT ... FROM eav WHERE category =3D '' ) icm USING ( invoice_id )

Add a sub-query join for each desired category.

I agree HStore looks cleaner for the query example you give.  One apparent a=
dvantage is that since each record contains a single value you can use addit=
ional columns to store properties (e.g. Units) whereas the hstore makes that=
 more difficult - though not impossible - since the keys are bot naturally r=
elated to each other.

=46rom an output perspective hstore looks good.  If there are no difficultie=
s on the data input process you might as well just go with it.  Given your v=
olume and use-case it really isn't going to matter which option you pick; th=
ough I'd suggest providing views related to each major category with only th=
e appropriate values and no access to the hstore column.

David J.