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=UjSQ2wYToG5tOX8bNKxsSuToC3ynNxrFiyZ/AR5NOX0Pe1+MaXZ2VBx+7AUcf0N9ytSrY07uS75eQp7zwumc0xrtR7q7p3m7BfL8cx1Md44K52An/f5lROzCu177cgrZ6Rzk0HPAN/A7ymwUGhv0YJ871XJjglethKPj8X6Q0pQ=
	;
References: <4EA29DDB.9000300@linos.es>
Mime-Version: 1.0 (iPad Mail 8J2)
In-Reply-To: <4EA29DDB.9000300@linos.es>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-Id: <00655F1E-08DC-42C5-B702-C8D332081557@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 08:53:52 -0400
To: Linos <info@linos.es>

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

> 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 al=
ready
> storing our vendor invoices in the database so calculate the monetary chan=
ge it
> is a no-brainer but we need to store special attributes for any of the inv=
oices
> 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, n=
umber
> of sms, etc..
>=20
> And much more types/variables, the number of variables can change, not eve=
ry day
> but still can change, i would like that they can be defined/changed from o=
ur
> application, so alter table to add columns don't seem the best way (still a=
n
> option though). We will have "generic" reports that will show us changes i=
n
> costs and specific reports for the types with "extended attributes" that w=
e want
> to compare.
>=20
> To compare values from this "extended attributes" i think we have two ways=
:
>    1- have them in columns and use standard SQL.
>    2- create the columns with a function that reads this attrs and create t=
he 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 in=
voice
> uses them.
>    2- a table related with the vendor invoices table for every type of inv=
oice
> with his specifics columns.
>    3- a key/value in a separate table related with the vendor invoices tab=
le 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 attrib=
utes
> of every type and give a more closed solution, apart from that 1- seems to=
 be a
> bit awkward and 2- would need the application that creates the query to kn=
ow
> with what table should join for every type (other point we will need to ch=
ange
> 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 fun=
ction
> that return rows as columns to compare them, with 4- given that i will sto=
re the
> attrs of every type in the database anyway i can use the operator -> (with=
 a
> CASE using operator ? returning 0 if the searched attr it is not in the hs=
tore)
> 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 mo=
re
> elegant (or correct) way to handle this situation, what would be your advi=
ce?
> Thanks.
>=20
>=20

Create a table with a single numeric column and multiple category columns.

( amount_value, amount_unit, amount_category, vendor_id )

If necessary each "amount_value" data type should have it's own table since t=
he processing logic will vary (I.e., you cannot subtract text or Boolean val=
ues).

You are , in effect, creating multiple tables but combining them into one an=
d using the category column to distinguish between them.

David J.=