On Wednesday 16 Mar 2016 09:52:43 Jeremy Evans wrote: > On Wednesday, March 16, 2016 at 3:37:20 AM UTC-7, gimoh wrote: > > 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 ... > 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).
Ah, OK, in our case we're more commonly looking up an attribute for a specific entity (e.g. SELECT value FROM eav WHERE entity = 'banana' AND attribute = 'colour') or all attributes of a specific entity (e.g. SELECT attribute, value FROM eav WHERE entity = 'banana'). In the former case I wasn't sure how that would translate to jsonb. But, OK, I see your point, if you need to find entities by attribute+value or some other more complicated lookups, and I'd also guess if you needed types other than strings then jsonb/hstore will be much easier. -- 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.
