> For the complete documentation index, see [llms.txt](https://avinandanbanerjee99.gitbook.io/system-design-notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://avinandanbanerjee99.gitbook.io/system-design-notes/databases/dynamodb-data-modeling.md).

# DynamoDB Data Modeling

* Concept of query first modeling - you model your data according to the access patterns for your domain. This somewhat limits the variety of queries that can be executed. 

* Hence NoSQL works best for OLTP cases where we have predictable queries. In OLAP situations, there may be cases where the nature of the analytical query is not known beforehand.

* Primary key is needed for each query. It can have
  * Partition Key (Simple Primary Key)
  * Partition Key + Sort Key (Composite Primary Key)

* Secondary Indexes support new access patterns
  * A global secondary index (GSI) creates a replica of the table with new partition and sort key

* It is recommended to have as few tables as possible in DynamoDB - following principle of locality of reference. This enables different related entities stored within the same table to be fetched together as part of a query - bypassing a join. We are trying to make heterogenous partitions.

* It is important to partition your data well - avoid "hot spots" of partitions. Come up with a partition key that is uniformly distributed.
  * One approach is to randomly add a suffix to partition key when writing - PK\_random
  * This is bad as when reading, we don't know the exact partition key - and have to query a range and then merge back
  * The better approach is to make a deterministic suffix, eg based on the hash of another attribute - PK\_hash(attrId)

* Partition keys can be for the form `ENTITY#entityId`
  * This allows you to identify entities very easily

* Sort keys enable filtering, with constructs such as `begins_with, < and >`

* Composite sort keys can enable one to many relationships, eg&#x20;
  * SK = `[country]#[region]#[state]#[county]#[city]#[neighborhood]`
  * We can now filter at any amount of granularity (eg. check rows for `INDIA#EAST`)
  * A common pattern is to make such a composite sort key and add it into GSI

* SKs can be used for version control - v0 always has the latest data

![](/files/lqsJpHXBmnFzoHLGiuYX)

* The Global Secondary Index can create a replica of your table, wherein there may be a new partition and sort key than the original table - it is used to support new access patterns
* It is important to project only the attributes you need into the GSI - else it will incur unnecessary cost as it is a replica
* Concept of sparse indexes - since DynamoDB is schemaless, only a few rows may have an optional attribute upon which a GSI is declared. Eg. Champions of duels in a game.
  * This enables us to quickly query this small subset of data - the GSI table only has these
  * Additionally the GSI attribute need not be like a state - it may have something useful like num. kills, with which we can sort
* A common pattern is to swap or invert the partition and sort keys in a GSI. In normal tables - we have all denormalized entities available together for a partiton. In GSI table - we have all rows for an entity (across the original partitions) easily available

#### One to Many Relationships

* If the "many" entity is constrained in num, eg max. 5 addresses, it is OK to keep a list of the "many" entity as an attribute of the "one" - cocept of denormalization
  * Eg. User has a list of Address in userAddresses attribute
  * Above is assuming no access pattern expects to query the "many" attribute directly
* Another approach - keep the many attribute as part of the sort key, eg.

| Partition Key | Sort Key |
| ------------- | -------- |
| USER#avi      | ORDER#33 |
| USER#avi      | ORDER#34 |

* If that "many" item now has other attributes as many - try using the inverted index pattern with GSI

| Partition Key ( GSI SK) | Sort Key (GSI PK) |
| ----------------------- | ----------------- |
| ITEM#1                  | ORDER#3           |
| ITEM#2                  | ORDER#33          |