Re: advice on how to store variable attributes
От | Linos |
---|---|
Тема | Re: advice on how to store variable attributes |
Дата | |
Msg-id | 4EA2E3A8.8090203@linos.es обсуждение исходный текст |
Ответ на | Re: advice on how to store variable attributes (David Johnston <polobo@yahoo.com>) |
Ответы |
Re: advice on how to store variable attributes
Re: advice on how to store variable attributes Re: advice on how to store variable attributes |
Список | pgsql-sql |
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 cannotsubtract text or Boolean values). > > You are , in effect, creating multiple tables but combining them into one and using the category column to distinguishbetween 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'), (SELECTamount_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.
В списке pgsql-sql по дате отправления: