How can I model this data in DynamoDB for a Library App

user_8717781 0
Viewed 104

I have two entities, Books and Authors with a strict one-to-many relationship (many-to-many relationship not required for my use case)

The access patterns I want to satisfy are:

  • Get Author Info by Author Name
  • Get Book Info By just ISBN
  • Get all Books records by an Author using Author Name.

Do I need any GSI given the constraint that I can make only a single request to DB when adding a Book or an Author, and fulfill above three access patterns also with a single request?

If my Author Entity uses this key schema:

  • Partition Key: AUTHOR#XYZ
  • Sort Key: AUTHOR#XYZ

and for Book Entity I use

  • Partition Key: BOOK#123
  • Sort Key BOOK#123

I can get author info by name and book info by ISBN easily. How do I get the 3rd access pattern, entire book data by author name?

Two approaches I thought of:

  1. Have a third entity in the table with PK AUTHOR#XYZ, SK BOOK#123, and use BEGINS_WITH(SK, 'BOOK') but in this approach, when adding a book to DB, I will have to write two items, PK BOOK#, SK BOOK# for getting book by just ISBN and PK AUTHOR#, SK BOOK# for getting all books by author, and the book info will be duplicated in both items.
  1. Add an attribute GSIAuthorName to Book entity when adding a book, and create a GSI with PK GSIAuthorName (AUTHOR#XYZ) and SK being PK of Book entity (BOOK#123). But in this the issue is, in projections I will have to select ALL, since I want all book info attributes by author name, and need to fetch in single query to the GSI, so entire Book Entity will be duplicated in this GSI.

Is there an easier way to model this data?

amazon-dynamodb nosql amazon-web-services data-modeling database-design
1 Answers

Since you're trying to have two different access patterns for a single entity that require a different partition key value, there is basically only the two options you have identified correctly.

Your design seems to only work for books that have a single author. In the real world that's not sufficient. There are plenty of books with multiple authors such as "The Dictator's handbook" by Bruce Bueno de Mesquita and Alastair Smith - your data model might want to account for that. Author <-> Book isn't One-to-Many, it's Many-to-Many.

I'd go for something like this which uses a Global Secondary Index. It's very close to your second suggestion.

PK SK GSI1PK GSI1SK type attributes
AUTHOR#ALASTAIR SMITH AUTHOR#ALASTAIR SMITH author name, birthdate, ...
AUTHOR#BRUCE BUENO DE MESQUITA AUTHOR#BRUCE BUENO DE MESQUITA author name, birthdate, ...
BOOK#978-1610391849 AUTHOR#ALASTAIR SMITH AUTHOR#ALASTAIR SMITH BOOK#978-1610391849 book title, publisher, author,...
BOOK#978-1610391849 AUTHOR#BRUCE BUENO DE MESQUITA AUTHOR#BRUCE BUENO DE MESQUITA BOOK#978-1610391849 book title, publisher, author,...
  • Does this introduce data duplication? - Yes
  • Does this introduce complexity on writes? - Yes
  • Does it work in the real world? - Yes

The model I've chose allows you to fulfill the requirements:

  1. Get Author Info by Author Name: GetItem on the primary index with PK=AUTHOR#... and SK=Author#...
  2. Get Book Info by just ISBN: Query on primary index with PK=BOOK#... and limit 1
  3. Get all books for an Author: Query on GSI1 with PK=AUTHOR#

When you write a book, you need to add a book record for each author and potentially the author entries. For updates on a books info (which should be very rare) you first do the query as in 2) without the limit and then update each item that comes back.

Update

To address the requests for clarification in the comments:

  • If you require a strict One-to-Many relationship, I'd pick the second approach
  • Frequent writes are typically not a problem in your one-to-many case as long as you don't exceed the write throughput of a single partition, which is unlikely given the data. I don't see why you'd need frequent writes though.
  • The extra complexity is typically only a one-time penalty when you create your data access layer. The code for update_book_by_isbn will have to include the steps I outlined above and the create_book might store multiple records.
user_64858810
Related