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:
kilometersof 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
thisattrs.
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.
Regards,
Miguel Angel