From: David Johnston <polobo@yahoo.com>
To: Linos <info@linos.es>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: advice on how to store variable attributes
Date: Sat, 22 Oct 2011 08:53:52 -0400
Message-ID: <00655F1E-08DC-42C5-B702-C8D332081557@yahoo.com> (raw)
In-Reply-To: <4EA29DDB.9000300@linos.es>
References: <4EA29DDB.9000300@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 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, info@linos.es
Subject: Re: advice on how to store variable attributes
In-Reply-To: <00655F1E-08DC-42C5-B702-C8D332081557@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox