On Oct 22, 2011, at 6:41, Linos <i...@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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
