3

As per my data model, I need to store many to many relationship data items in dynamodb Example : I have a field called studentId and every studentId will have several subjects assigned to him.

Requirement :

  1. So that for a given studentId, I need to store all the subjects. I would need to get all the subjects assigned to a given student.

  2. Similary, for a given subjectId, I need to know the studentIds whom that subject has been assigned to.

am planning to store this in dynamoDb as follows :

  • Table1 : StudentToSubjects :
    • Hash Key : StudenId,
    • RangeKey: subjectId

so that if I query using only primaryKey, it would give me all the rows having that primary key and all the different hash keys.

Secondary Key as

  • secondary HashKey: subjectId
  • Secondary RangeKey: studentId

I wanted to know if this makes sense or the right thing to do. Or there are better ways to solve this problem.

Scorpion
  • 633
  • 3
  • 11
  • 24

1 Answers1

3

Your Design looks OK but you need to think it through before finalizing it, let say you have implemented this design and after 10 years when you will query the table for particular subject, you will get all the students of past 10 years which you might not need (when you query using secondary table-GSI).

I would probably go with following

Student Master:

Hash Key: studentId
subjectIds (Number-set or String-set)

Subject Master:

Hash Key: subjectId
Range Key: Year
studentIds (Number-set or String-set)

Advantage of this would be you will consume less queries, for particular subject or student you will consume only 1 read (if the size is less then 4kb).

Again this is just scratching a surface think of all the queries before finalizing the Schema.

Edit: You don't need to repeat the studentId it will remain unique. it would look something like this

studentId -> s1
subjectIds -> sub1,sub2,subN (This is set)

studentId -> s2
subjectIds -> sub3,sub4

Following is the data type link you can refer http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/DataModel.html#DataModel.DataTypes

Harshal Bulsara
  • 7,898
  • 4
  • 43
  • 61
  • What is Number-set or String-set? Can you provide more details on this. So in StudentMaster : How would you store subjectIds? as value? Can we have multiple rows with same studentId as hash key in that case? – Scorpion Dec 08 '15 at 00:46
  • Are you suggesting to use the set.collection in the value part of the dynamodb table for the item? Please correct me if am wrong in getting your point but if this is what you are suggesting, I see a drawback there. The size of set is not known and it would grow with time and we cannot have item record size of > 400Kb in DDB. Hence its not recommended to store collections as part of your value in key value pair. – Scorpion Dec 08 '15 at 20:58
  • 400 kb is big enough to fit your requirement, and I don't see a scenario where you will add like 1000's of subject to a single student. By the way in my app. I have bigger list size and and at that time the limit was 64kb and it was working fine :) – Harshal Bulsara Dec 09 '15 at 02:41
  • look at this link http://stackoverflow.com/a/4100347/2811189 I guess it should be able to store your data – Harshal Bulsara Dec 09 '15 at 05:35
  • That was just an example where I used studentId and subject Harshal. Please assume that i can have lots of values for a given key and hence i was asking this question. Also it is not a good idea to store such collections in value because then you need to select size of your item to something around 200-400KB and DDB store gets very costly to use based on size of item. Also, you might not also be using all the size which you might have reserved because its possible that there might be only a few scenarios where you are using the full size. – Scorpion Dec 10 '15 at 18:29