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=ptQzzdg4k6N6KQqViAvbAi27KJlIiA94CUR5WUvY/BYak2WtJcuShTLcGY7o3TpuLDVq2xtxG4ngzzsAfRpWCtpwgQ/Rf4FHWwQWCVdeMG0o/gaqF8+fs0wYpYvUuZN6BjpnGLN45jpmdohxLKUrBXjPzE51eolf5U1NUBkI0VU=
	;
References: <4EA29DDB.9000300@linos.es>
	<00655F1E-08DC-42C5-B702-C8D332081557@yahoo.com>
	<CAFj8pRDbAySB_mP53eiJycyuEuFHHEnykL-jRZVQF+1apt3PJw@mail.gmail.com>
Mime-Version: 1.0 (iPad Mail 8J2)
In-Reply-To: 
 <CAFj8pRDbAySB_mP53eiJycyuEuFHHEnykL-jRZVQF+1apt3PJw@mail.gmail.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-Id: <DABCF799-0E42-49AB-9650-66838B764C34@yahoo.com>
Cc: Linos <info@linos.es>,
	"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 10:28:48 -0400
To: Pavel Stehule <pavel.stehule@gmail.com>

On Oct 22, 2011, at 10:07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2011/10/22 David Johnston <polobo@yahoo.com>:
>> 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=
 information.
>>>=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 creat=
e 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 i=
nvoice
>>> with his specifics columns.
>>>    3- a key/value in a separate table related with the vendor invoices t=
able 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 attr=
s.
>>>=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
>=20
> This is EAV model - is good for smaller datasets, for larger datasets
> is problematic. There is second possibility - using a "hstore" contrib
> module - that emulates HASH table - It has better for larger datasets.
>=20
> Regards
>=20
> Pavel Stehule

Store was mentioned by the OP.

Any suggestions on where the line between small and large is drawn?

Partitions could help in the larger cases.

My personal first choice is to use separate tables.  If going EAV route plan=
 on eventually moving to the separate table route and at least try to make m=
igration relatively easy.

Since both models capture the same data the decision at least partially rest=
s upon the immediate use-cases for the data.  Lacking use-cases and size par=
ameters recommendations are difficult to make.

I have not used hstore so my ability to recommend it is limited.  My main "c=
oncern" is the fact that the data is less visible and always stored as text.=
  I see hstore as being useful for situations where the attributes are varia=
ble but this use case seems to have fairly well-defined attributes.

David J.


>=20
>>=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.
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>=20
>=20
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql