• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Is this an SQL Antipattern?

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?

--
DLL
 
author
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

David Lee Lambert wrote:...newline-separated list of "key=value" pairs...



This is what Martin Fowler might classify as the Serialized LOB pattern: http://www.martinfowler.com/eaaCatalog/serializedLOB.html

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.
 
Author
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
+1 for the Date book; it's *excellent*.

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.)
 
Bill Karwin
author
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 4116
72
Mac TypeScript Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is more frequently used (specially in the BPM world) now to store the data in the DB (as a blob), which allows adding more properties to objects and still use the same table to store the data.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic