redis database design - determining the proper data type and creating secondary indexes

Question

i have the following data that looks like this:

{
location: 'zimbabwe',
phone_num: 123-123-1234,
ext: 2222
}

or

{
location: 'puerto rico',
phone_num: 222-222-2222,
ext: 8888
}

My end users need to be able to query a REST API and send something like this:

http://myapp/internalext/123-123-1234

that should return an internal extension value of 2222.

But I also need to be able to support a query like this:

http://myapp/phonenumber/[email protected]

That should return to me a value of 123-123-1234

In order to be able to support queries like this, I'd like to know what the best way is for creating my data in redis. I have to create a node-redis web api.

So far, I've tried creating data like this:

127.0.0.1:6379> set phone:1
"{\"id\":1, \"locid\":1, \"loc_name\":\"zimbabwe\", \"extension\":\"2222\", \"e164\":\"1231231234\"}"

And then I created a secondary index referencing the same phone object:

127.0.0.1:6379> hset phone:lookup:e164 1231231234 1

Now, when I query, I have to do two lookups to find the ifnormation I want. So if the user passes me the full phone number, I have to do the following queries:

1. First lookup using the e164 as key:
127.0.0.1:6379> hget phone:lookup:e164 1231231234
"1"

2. now you know that it's the first key in the "phone" set(?? i dunno if this is the terminology)

127.0.0.1:6379> get phone:1
 "{\"id\":1, \"locid\":1, \"loc_name\":\"zimbabwe\", \"extension\":\"2222\", \"e164\":\"1231231234\"}"
127.0.0.1:6379>

QUESTIONS

Is this the best way to organize / create my redis data for these types of GET requests? I'm just reading about hashes. But I'm not familiar enough to know which way to proceed. Also, given the above data, how would i request to see all phone numbers and their data?

Need to know more about your requirement. Is it always getting extn from phone number and vice versa, or you might need getting location from extn or phone number? — Karthikeyan Gopall
– Karthikeyan Gopall, Commented Sep 9, 2016 at 6:34
@KarthikeyanGopall the main translation will be between phone number and extn... but once I've identified the right "record" I may need to return more than just the one field. But because the record is so small / not too many fields, i may just return everything, unless you see some problems? — Happydevdays
– Happydevdays, Commented Sep 9, 2016 at 12:37

fooBear · Accepted Answer · 2016-10-24 17:39:34Z

Could it be that in your GET http://myapp/phonenumber/[email protected] you are looking for a PSTN number for a given URI (not simply for a given extension)?

If that's the case (and I'm only guessing here based on your key named 'e164') the lookup would be for the phone number whose URI is sip:[email protected] and your application is expected to return the phone number 123-123-1234. In other words, what is the DID for company ABC so call can be completed over PSTN and not over IP...

> get phone:1
"{
  \"id\":1, 
  \"locid\":1, 
  \"loc_name\":\"zimbabwe\",
  \"iso3166\":\"ZW\",
  \"loc_idd\":\"263\",
  \"extension\":\"2222\", 
  \"e164\":\"4.3.2.1.3.2.1.3.2.1.3.6.2.e164.arpa.\",
  \"uri":\"sip:[email protected]\",
  \"pstn\":\"+2631231231234\",
  \"ui_disp_long\":\"+263-123-123-1234\",
  \"ui_disp_short\":\"123-123-1234\"
}"
>

As far as your REST/redis question is concerned, I do concur with @noun.

noun · Accepted Answer · 2016-09-09 17:14:31Z

0

I would use a HMSET to store the entire record, then simple key/value to query the data. For example:

HMSET 123-123-1234 location "zimbabwe" phone_num "123-123-1234" ext "2222"
HMSET 222-222-2222 location "puerto rico" phone_num "222-222-2222" ext "8888"

SET ext:2222 123-123-1234
SET ext:8888 222-222-2222

To get 123-123-1234 you use:

GET ext:2222

Given the number to get ext or location:

HMGET 123-123-1234 ext 
HMGET 123-123-1234 location

edited Sep 9, 2016 at 17:14

answered Sep 8, 2016 at 21:02

noun

3,7552 gold badges27 silver badges27 bronze badges

3 Comments

Happydevdays Over a year ago

what would the queries look like in this case?

Happydevdays Over a year ago

another question would be in the above example, could "myhash1" just be the phone number? And that way, i could get rid of the SADD you have to map phone_num to myhash?

noun Over a year ago

To your second comment the answers are yes and yes. I will update the answer with the queries.

Collectives™ on Stack Overflow

redis database design - determining the proper data type and creating secondary indexes

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related