I am a new in Redis and key-value database. Could you advise me about correct realization this relational approach in redis.
I have relational table with two key to one value:
-master_id; -slave_id; -value.
master_id | slave_id | value
1 | 1 | val1
2 | 1 | val2
Usually access to values is done by master_id field, but sometimes it is necessary make selection (or deleting) by slave_id field. Thus we can obtain a single value by two fields (master_id or slave_id). In redis, as I understand every single key mean one value. What the best way to perform this, without duplication of value? NOTE: value field must be a SET or list type.
In redis we have:
1:1 val1 2:1 val2
And I can make access to value only by a full key 1:1 or 2:1. I can't make something like this DEL * :1 (delete all keys where second part equal 1) or GET 1: * (get all keys where first part of keys equal 1)
To avoid duplication of values, you can use plain key/value pairs like this:
master_id : value</li> <li>
slave_id : master_id</li> </ul>
This has the drawback that you have to query twice the server to get/delete/modify a value by slave_id. (You first query for the slave_id, the server responds with the master_id if any and then you query master_id to do stuff with the value associated). This will not be an issue as soon as LUA scripting arrives, though.
In the example, I do not understand if the primary key is just master_id or (master_id,slave_id). I assume the latter.
It is usually pointless to try to map relational concepts to key/value stores. Redis is a data structure server, so you need to think in term of data structure and access path. You want to:
<li>store the values once</li>
<li>be able to access the values from the master_id field</li>
<li>be able to access the values from the slave_id field</li>
<li>a numeric key which will identify the values</li>
<li>a set per master_id to index the corresponding values</li>
<li>a set per slave_id to index the corresponding values</li>
SET value:1001 val1 SET value:1002 val2 SADD master:1 1001 SADD master:2 1002 SADD slave:1 1001 1002
To retrieve the values corresponding to a given master id:
SMEMBERS master:id MGET <result of the previous command with a value: prefix>
To retrieve the values corresponding to a given slave id:
SMEMBERS slave:id MGET <result of the previous command with a value: prefix>
To retrieve the values corresponding to master id1 and slave id2:
SINTER master:id1 slave:id2 MGET <result of the previous command with a value: prefix>
It can be further optimized to reduce the number of roundtrips by using the SORT command. See an example in this answer.
Of course, if master_id is actually the primary key, then it can be simplified since there is no need to introduce an additional key to identify the values.