So in Joomla! 1.5, just about every table has a "params" column that's basically a newline-separated list of "key=value" pairs. I've written a Hibernate type to expose it as an instance of Properties, and Postgres and MySQL "C" stored procedures to make it easier to parse a value from that column. There's not much I can do about Joomla! (there are thousands of installations in the world), but it seems like that isn't the "relational" way to store such data. Is there a better solution?
It's a question of the lesser of the available evils. The alternative way some people store key/value pairs is the awful Entity-Attribute-Value design, which is an SQL antipattern. Collecting the key/value pairs into a blob and storing them all together is actually preferable to EAV.
You'll still find it inconvenient to use the key/value pairs as discrete attributes in SQL expressions, simply because SQL doesn't offer syntax to address the individual sub-elements. You just have to treat the params column as a kind of black box, at least from the context of SQL queries. You can fetch the whole list of key/value pairs and access them using application code once you've deserialized them into an application-side Map of some kind. You've described that you do this with Hibernate or with stored procedures, but that's kind of a lot of work, and you still can't reference one of these sub-elements simply in a WHERE clause.
Read "SQL and Relational Theory" by C. J. Date (probably the best living authority on relational theory). He points out that a single attribute can have structure. You could even store a relation in a single column. He calls these relation valued attributes, and that starts to resemble a set of key/value pairs. Most SQL vendors don't support this concept, but it's not a violation of relational theory, as long as the given column stores the same type of relation on all rows.
I'm not a huge fan of columns-with-structure, though, simply because querying on them is... problematic. It also seems to depend on the nature of what's being put in there--is it an arbitrarily large number of wildly disparate properties, or is it a subset of a known set?
(IANADBEBAM... I Am Not A DataBase Expert By Any Means.)
Joined: Aug 02, 2010
Certainly it depends on the type of relation used for the relation valued attribute. You can't just use arbitrary key/value pairs, with a potentially different set of keys in each row.
For it to be relational, you'd have to ensure that the same keys are present in every row. The easiest way to do this in an RVA is to make the keys column names, and the values would be data in a single row.
But if you're doing that, you might as well just store the properties in conventional columns instead of an RVA. The reason a product like Joomla stores the blob of key/value pairs is that they want to extend the set of keys without creating more columns. And that isn't supported by relational theory.