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.
Example:
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
slave_id : master_id
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:
- store the values once
- be able to access the values from the master_id field
- be able to access the values from the slave_id field
You need:
- a numeric key which will identify the values
- a set per master_id to index the corresponding values
- a set per slave_id to index the corresponding values
Example:
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.
