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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>