Recommendations on schema for reviews

Question

I need to store reviews and each review is actually made of multiple sub-reviews, represented as JSON it would look something like this:

```

{
    farm_reviews: [
        {
            user_id: 'AAAA',
            review_id: 'BBBB',
            date: '2022-03-26'
            name: 'Bobs Farm'
            animal_review: [
                cow: 8,
                chicken: 4,
                horse: 10
            ]
        },
        {
            user_id: 'AAAA',
            review_id: 'CCCC',
            date: '2022-03-25'
            name: 'Jims Farm'
            animal_review: [
                cow: 3,
                chicken: 2,
                horse: 6
            ]
        },
        ...
    ]
}
```

What's the most efficient way to store this data so I can show user AAAA his 10 most recent farm reviews while at the same time supporting the ability to show him every farm review he rated a cow greater than 5?

Answer

Hi - It depends on the database type (assuming this is for nosql) and overall access pattern. Also assuming this is for DynamoDB, you may want to review best practices and data modeling techniques based on end to end requirements.

1. [Best practices for designing and architecting with DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/best-practices.html)
2. [AWS re:Invent 2021 - Data modeling with Amazon DynamoDB](https://www.youtube.com/watch?v=yNOVamgIXGQ)

You may also think about selecting the right partition key like https://aws.amazon.com/blogs/database/choosing-the-right-dynamodb-partition-key/ and also indexes https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/SecondaryIndexes.html

What you have a basic structure is fine assuming that these reviews can be replaced or may be cumulative in nature meaning user_id: 'AAAA' may provide a new review for name: 'Bobs Farm' with a new review_id or same and hence the history may co-exist or replaced. But you have to pull only latest . So think on all partition keys and indexes. See process here https://docs.aws.amazon.com/prescriptive-guidance/latest/dynamodb-data-modeling/step3.html

Recommendations on schema for reviews

Relevant content