CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodbnosql
Mike Rifgin
Mike Rifgin

Reputation: 10761

DynamoDb database design

I'm new to DynamoDb and noSql in general.

I have a users table and a notes table. A user can create notes and I want to be able to retrieve all notes associated with a user.

One solution I've thought of is every time a note is saved the note id is stored inside a 'notes' attribute inside the user table. This will allow me to query the users table for all note id's and then query notes using those id's: 

UserTable:
UserId: 123456789
notes: ['note-id-1', note-id-2]

NotesTable
id: note-id-1
text: "Some note"

Is this the correct approach? The only other way I can think is to have the notes table have a userId attribute so I can then query the notes table based on that userId. Obviously this is the sort of approach is more relational.

Upvotes: 1

Views: 171

Answers (1)

Luc Hendriks
Luc Hendriks

Reputation: 2533

I would take the approach at the end of your question: each note should have a userId attribute. Then create a global secondary index with userId as primary key and noteId as sort key. This way you can also query on userId, by doing a query on that index.

If you do it the way you suggested, you always need two queries to get the notes of a user (first get the notes from the user table and then query on the notes table). Also, when someone has N notes you would need to do N queries, this is going to be expensive if N is large.

If you do it the way in this answer, you need one query to get all notes of a user (I'm assuming no pagination) and one to get the user information. Will never be more than 2.

General rule of thumb:

  • SQL: storage = expensive, computation = cheap
  • NoSQL: storage = cheap, computation = expensive

So always try to need as little queries as possible.

Upvotes: 3

Related Questions