2

I have a simple relational model that I would like to create in DyanamoDB using a single table design. The model is for a restaurant review app:

The entities are:

  • Users => user` has one-to-many reviews
  • Review => restaurant has one-to-many reviews
  • Restaurant => review has one `user

A user has an email and displayName

Access Patterns:

  1. List all reviews for a single user
  2. List reviews for a restaurant
  3. Find all reviews for a restaurant with the displayName of the user who created the review.

I'm struggling with 3: Find all reviews for a restaurant with the displayName of the user who created the review.

I could store displayName on a review item but if my user updates the displayName I need to update all reviews with this displayName.

I'm struggling with how to connect restaurant review and user in a single query. The closest I've gotten is:

Hash Key    | Range Key               | Data
----------------------------------------------------------------
user 1      | review1                 | restaurant1
            | review2                 | restaurant3
            | review23                | restaurant4
            | profile                 | Kate Smith   | ksmith@gmail.com
user 2      | review5                 | restaurant1
            | review6                 | restaurant2
            | review7                 | restaurant4
            | profile                 | John Smith   | jsmith@gmail.com
restaurant1 | address#street#areaCode |
restaurant2 | address#street#areaCode |

I can satisfy patterns 1 and 2 using a GSI with the above design but not 3. I've also tried to flip the design so that reviews relate to restaurants but still can't get to satisfying pattern 3.

Is what I'm trying to do possible with a single table design, in a single query? Even if I could do this in two queries that would work.

Greatly appreciate any advice here!

Thomas Collins
  • 308
  • 1
  • 2
  • 9
  • I think the typical solution here is, as you say, to store the displayName on each review and update all items, when needed, using a transaction. Hopefully that's an infrequent task (and it probably is). The alternative RDBMS-like normalized solution requires you to join the data. Given that these queries are frequently executed while renaming a user is infrequently executed, you should benefit most of the time with the NoSQL approach. Certainly at high scale, anyway. – jarmod Nov 06 '20 at 01:15
  • Were you able to model this successfully? I too have a three entity many to many relationship. – callmekatootie Jul 17 '23 at 13:55

0 Answers0