On Oct 28, 2:55 pm, Nels Nelson <[email protected]> wrote: > > This is often an antipattern, at least if you ever need to query on > > multiple keys/values (e.g. find all books that have bar and foo and > > not baz in their glossary). > > Can you recommend reading for the correct pattern? Basically, a > pattern for implementing the fastest, most normalized method for > associating arbitrary name-value pairs to a table representing a > domain model distinct from the name-value pairs?
That's a tricky question. SQL as a language just wasn't designed for that sort of use case, and it doesn't really mesh with the relational model. The relational way is to give each name it's own column, but that doesn't scale well to support arbitrary names. PostgreSQL's hstore column type allows you to do exactly what you want (and with pl/v8 and a json type, you can or will be able to do that), but I'm not sure other databases have something similar. Serializing the value and deserializing in the application is OK in some use cases, but you lose the ability to query on the names and values. If you database supports an XML type, you could serialize to xml and you might still be able to query on the names and values. Often, the best way people have to deal with this situation is a separate table with a name column and a value column. You can still query on the data in that case, but the queries get cumbersome if you every need to reference multiple names/values at the same time. > However, the application that I am developing is not only under an > NDA, but it is written in JRuby, so any problems I encounter are often > very specific to the platform and the business requirements of the > implementation. Generalizing the code to make it shareable in the > forum is tricky, and sometimes simply not possible. > > I attempted to make things as general as possible with the "Book" > scenario, but I think I had trouble translating the issues. That's unfortunate, but understandable. The easiest way to troubleshoot such an issue is to attempt to replicate the simplest case in a standalone script, and then post that script here. But it is certainly possible that you can only replicate the issue in the application, in which case I won't be able to provide as much help. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
