File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Is this an SQL Antipattern? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Is this an SQL Antipattern?" Watch "Is this an SQL Antipattern?" New topic
Author

Is this an SQL Antipattern?

David Lee Lambert
Greenhorn

Joined: Oct 02, 2006
Posts: 7
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
Bill Karwin
author
Ranch Hand

Joined: Aug 02, 2010
Posts: 32
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.
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

+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
Ranch Hand

Joined: Aug 02, 2010
Posts: 32
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.
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

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.


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Is this an SQL Antipattern?