0

I want an efficient way to store ItemIDs and AttributeIDs for querying in DynamoDb.

There are a few thousand unique ItemIDs and 300 unique AttributeIDs. Each ItemID is usually associated with 20-100 AttributeIDs. Each AttributeID is associated with 20,000-40,000 ItemIDs There will be more AttributeIDs added with time.

I would like to be able to query to find:

  1. Return me the AttributeIDs associated with a given ItemID
  2. Find me the ItemIDs associated with a given AttributeID

My initial solutions are

  1. DynamoDB: Have a table where one ItemID matches an AttributeID. The primary key will be a Hash and Rage where the Hash = ItemID and Range = AttributeID. Have a global secondary index, Hash and Range where Hash= AttributeID and Range = ItemID.

  2. Same thing as above but with SQL, though this will result in about 9 million entries which will be slow for performance, especially as I add more AttributeIDs.

Is my solution okay or what is a better way of doing it? With the way I'm suggesting, would there be any difference in performance between an SQL and DynamoDB version (due to underlying table implementations)?

Community
  • 1
  • 1
tt_Gantz
  • 2,786
  • 3
  • 23
  • 43

1 Answers1

0

You should have

  1. table Item with index on ItemID
  2. table Atribute with index on AtributeID
  3. table Item Atribute with two fields ItemID, AtributeID with a composite index with both field.

And 9 millions is nothing for a db. Using the index you will find atributes for each item in milliseconds.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Would there be any advantage of using DynamoDB over RDS? – tt_Gantz Sep 25 '15 at 04:42
  • 1
    Maybe this other [**question**](http://stackoverflow.com/questions/13966368/aws-mysql-rds-vs-aws-dynamodb) help – Juan Carlos Oropeza Sep 25 '15 at 04:44
  • With DynamoDB, what benefit would the two tables have over having a primary hash and range key with ItemID and AttributeID respectively, and a global secondary index with AttributeID and ItemID as hash and range respectively? – tt_Gantz Sep 25 '15 at 04:44