Message-ID: <4EA2E3A8.8090203@linos.es>
Date: Sat, 22 Oct 2011 17:39:20 +0200
From: Linos <info@linos.es>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:7.0.1) Gecko/20111001 Thunderbird/7.0.1
MIME-Version: 1.0
To: David Johnston <polobo@yahoo.com>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: advice on how to store variable attributes
References: <4EA29DDB.9000300@linos.es>
	<00655F1E-08DC-42C5-B702-C8D332081557@yahoo.com>
In-Reply-To: <00655F1E-08DC-42C5-B702-C8D332081557@yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

El 22/10/11 14:53, David Johnston escribió:
> 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 information.
>>
>> We need to calculate the difference in costs for our operations, we are already
>> storing our vendor invoices in the database so calculate the monetary change it
>> is a no-brainer but we need to store special attributes for any of the invoices
>> 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..
>>
>> And much more types/variables, the number of variables can change, not every 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 (still 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.
>>
>> 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 the columns.
>>
>> So far i thin we have this options:
>>    1- a bunch of columns that would be null except when the type of the invoice
>> uses them.
>>    2- a table related with the vendor invoices table for every type of invoice
>> with his specifics columns.
>>    3- a key/value in a separate table related with the vendor invoices table 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.
>>
>> The first two have the problem of probably changes to the number of attributes
>> 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 know
>> with what table should join for every type (other point we will need to change
>> if we want to create new invoices types).
>>
>> The last two have his own problems too, with 3 i will need to create a function
>> that return rows as columns to compare them, with 4- given that i will store 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 hstore)
>> but still don't seem a clean solution for me.
>>
>> For me it seems i am missing something, probably any of you have a much more
>> elegant (or correct) way to handle this situation, what would be your advice?
>> Thanks.
>>
>>
> 
> 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 the processing logic will vary (I.e., you cannot subtract text or Boolean values).
> 
> You are , in effect, creating multiple tables but combining them into one and using the category column to distinguish between them.
> 
> David J.

Yeah, thanks for the advice David, if i understand you. this is (much better
explained) my option 3, i have used this format any times for configuration
tables with great success.

I am not speaking about much data, maybe 200~300 invoices every month so this
should not be a problem in a long long time, the problem i see with this way of
doing things it is how should i in the report queries get this values as columns
to compare change in time? something like this?

SELECT inv.invoice_id,
              inv.total,
	      (SELECT amount_value
               FROM vendor_invoices_attrs
               WHERE invoice_id  = inv.invoice_id
                  AND amount_category = 'international call minutes'),
             (SELECT amount_value
               FROM vendor_invoices_attrs
               WHERE invoice_id  = inv.invoice_id
                  AND amount_category = 'national call minutes')
FROM vendor_invoices AS inv

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

For me the last option seems preferable but maybe i am missing a better way to
get the first result or an added problem of hstore (other than datatypes stored
that i think i could check for validity in application or database regardless of
not been integrated in hstore).

Regards,
Miguel Angel.