How do you mix SQL DB vs. Key-Value store (i.e. Redis)

Question

I'm reviewing my code and realize I spend a tremendous amount of time

1. taking rows from a database,
2. formatting as XML,
3. AJAX GET to browser, and then
4. converting back into a hashed javascript object as my local datastore.

On updates, I have to reverse the process (except using POST instead of XML.)

Having just started looking at Redis, I'm thinking I can save a tremendous amount of time keeping the objects in a key-value store on the server and just using JSON to transfer directly to JS client. But my feeble mind can't anticipate what I'm giving up by leaving a SQL DB (i.e. I'm scared to give up the GROUP BY/HAVING queries)

For my data, I have:

• many-many relationships, i.e. obj-tags, obj-groups, etc.
• query objects by a combination of such, i.e. WHERE tag IN ('a', 'b','c') AND group in ('x','y')
• self joins, i.e. ALL the tags for each object WHERE tag='a' (sql group_concat())
• a lot of outer joins, i.e. OUTER JOIN rating ON o.id = rating.obj_id
• and feeds, which seem to be a strong point in REDIS

How do you successfully mix key-value & SQL DBs?

For example, is practical to join a large list of obj.Ids from a REDIS set with SQL data using a SQL RANGE query (i.e. WHERE obj.id IN (1,4,6,7,8,34,876,9879,567,345, ...), or vice versa?

ideas/suggestions welcome.

Answer 1

You may want to take a look at MongoDB. It works with JSON style objects, and comes with SQL like indexing & querying. Redis is more suitable for storing data structures likes lists & sets, when you want a simple lookup instead of a complex query.

Answer 2

Now that the actual problem is more defined (i.e. you spend a lot of time writing repetitive conversion code to move from one layer/representation to the next) maybe you could consider writing (or googling for) something that automatizes this, maybe?

Googles returns plenty of results for "convert table to XML" (and the reverse), would this help? Would something going directly from table to key/value pairs be better? Have you tried tackling this problem in a generalized way?