{"id":1602,"date":"2020-08-24T13:18:35","date_gmt":"2020-08-24T20:18:35","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=1602"},"modified":"2020-08-25T10:01:43","modified_gmt":"2020-08-25T17:01:43","slug":"how-to-handle-reference-tables-in-cosmos-db-that-are-common-in-the-relational-database-world","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/how-to-handle-reference-tables-in-cosmos-db-that-are-common-in-the-relational-database-world\/","title":{"rendered":"Azure Cosmos DB for the SQL Professional – Referencing Tables"},"content":{"rendered":"

I had a previous blog comparing Azure Cosmos DB to a relational database<\/a> and one topic that it did not address that I want to now is how to handle reference tables that are common in the relational database world.<\/p>\n

A big difference with Azure Cosmos DB, a NoSQL database, compared to a relational database is you will create a denormalized data model<\/em>.\u00a0 Take a person record for example.\u00a0 You will embed all the information related to a person, such as their contact details and addresses, into a single JSON document.\u00a0 Retrieving a complete person record from the database is now a single read operation against a single container and for a single item.\u00a0 Updating a person record, with their contact details and addresses, is also a\u00a0single write operation against a single item.\u00a0 By denormalizing data, your application typically will have better read performance and write performance and allow for a scale-out architecture since you don’t need to join tables.<\/p>\n

(Side note: “container” is the generic term. Depending on the API, a specific term<\/a> is used such as “collection” for the Core (SQL) API). Think of a container as one or more tables in the relational world. Going a little deeper, think of a container as a group of one or more “entities” which share the same partition key. A relational table shares a schema, but containers are not bound in that way.)<\/em><\/p>\n

Embedding data works nicely for many cases but there are scenarios when denormalizing your data will cause more problems than it is worth.\u00a0 In a document database, you can have information in one document that relates to data in other documents. While there may be some use cases that are better suited for a relational database than in Azure Cosmos DB (see below<\/a>), in most cases you can handle relationships in Azure Cosmos DB by creating a normalized data model<\/em> for them, with the trade-off that it can require more round trips to the server to read data (but improve the efficiency of write operations since less data is written).\u00a0 In general, use normalized data models to represent one-to-many relationships or many-to-many relationships when related data changes frequently. The key is knowing whether the cost of the updates is greater than the cost of the queries<\/em>.<\/p>\n

When using a normalized data model, your application will need to handle creating the reference document.\u00a0 One way would be to use a change feed<\/a> that triggers on the creation of a new document – the change feed essentially triggers an Azure function<\/a> that creates the\u00a0 relationship record.<\/p>\n

When using a normalized data model, your application will need to query the multiple documents that need to be joined (costing more money because it will use more request units<\/a>), and do the joining within the application (i.e. join a main document with documents that contain the reference data) as you cannot do a “join” between documents within different containers in Azure Cosmos DB (joins between documents within the same container can be done via self-joins<\/a>).\u00a0 Since every time you display a document it needs to search the entire container for the name, it would be best to put the other document type (the reference data) in a different container so you can have different partition keys for each document type (read up on how partitioning<\/a> can make a big impact on performance and cost).<\/p>\n

Note that “partitioning” in a relational database management system (RDBMS) compared to Azure Cosmos DB are different things: partitioning in Azure Cosmos DB refers to “sharding” or “horizontal partitioning<\/em>“, where replica sets which contain both the data\u00a0and copies of compute (database) resources operating in a \u201cshared nothing\u201d architecture (i.e. scaled \u201chorizontally\u201d where each compute resource (server node) operates independently of every other node, but with a programming model transparent to developers). Conversely, what is often referred to as \u201cpartitioning\u201d in a RDBMS is\u00a0purely a separation of\u00a0data into separate file groups within a\u00a0shared compute (database) environment. This is also often called \u201cvertical partitioning<\/em>\u201d.<\/p>\n

Another option that is common pattern for NoSQL databases is to create a separate container to satisfy specific queries.\u00a0 For example, having a container for products based on category and another container for products based on geography.\u00a0 Both of those containers for my query\/app are being sourced from one that is my “main” or “source” container that is being updated (front end, or another app) and the change feed attached to that pushes out to my other containers that I use for my queries.\u00a0 This means duplicating data, but storage is cheap and you save costs to retrieve data (think of those extra containers as covering indexes in the relational database world).<\/p>\n

Since joining data can involve multiple ways of reading the data, it’s important to understand the two ways to read data using the Azure Cosmos DB Core (SQL) API:<\/p>\n