On Wednesday, March 16, 2016 at 3:37:20 AM UTC-7, gimoh wrote: > > On Monday 14 Sep 2015 12:28:48 Jeremy Evans wrote: > > In general, I don't > > recommend the EAV approach used by rails-settings except as a last > resort. > > If you are using PostgreSQL, a jsonb/hstore column for storing settings > is > > a better approach. > > Just out of curiosity, how would you use jsonb/hstore as alternative to > EAV > (i.e. something like a table with (entity, settings) columns?) and what > advantages do you see in it over EAV? >
An EAV has three columns, one each for the Entity (foreign key), Attribute (attribute name), and Value (attribute value). This results in separate rows per attribute, instead of having all values in a single column in the main table In terms of how you would use it, instead of creating separate rows for each attribute, you just create an hstore/json value with the attribute names as keys. Sequel provides pg_hstore and pg_json extensions for that. Let's say you want to find entities where attribute A = 1 and (attribute B = 2 or (attribute B does not exist and attribute C = 3)). With a jsonb/hstore column, such a query is fairly easy (Sequel provides pg_hstore_ops and pg_json_ops extensions to help with querying). Can you write the correct query when using an EAV table? While not impossible, it is more difficult and will not perform nearly as well (separate subqueries/joins per attribute). Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
