Re: advice on how to store variable attributes

>
> 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

This should be rewritten little bit more readable

SELECT inv.invoice_id, inv.total,
a1.amount_value,
a2.amount_value
FROM vendor_invoices inv,
vendor_invoices_attrs a1,
vendor_invoices_attrs a2
WHERE a1.id = inv.id AND a2.id = inv.id
AND a1.amount_category = 'international call minutes'
AND a2. amount_category = 'national call minutes'

But it feature of EAV and similar models - you has a less database
objects and more complex queries.

>
> 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.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>