![\"Diagram](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM01-300x109.png\")
<\/p>\n
Each server has its own disk storage and CPU, just like any machine. And that means that \u2013 effectively \u2013 you get unlimited storage and unlimited throughput. There\u2019s no practical limit to the number of servers in the cluster, meaning no limit on disk space or processing power for your Cosmos DB container.<\/p>\n
Non-relational<\/h3>\n
Think about how things work in the relational<\/em> world, where we store data in rows<\/em>. We focus on how those rows get joined along primary and foreign keys, and we rely on the database to enforce constraints on those keys.<\/p>\n But in the non-relational<\/em> world, we store data in documents \u2013 that is JSON documents. Now there\u2019s certainly nothing you can store in a row that you can\u2019t store in a JSON document, so you could absolutely design a highly normalized data model with documents that reference other documents on some key, like so:<\/p>\n Unfortunately, this results in a very inefficient design for Azure Cosmos DB. Why? Because again, Azure Cosmos DB is horizontally scalable, where documents that you write to a container are very likely to be stored across multiple physical servers behind the scenes:<\/p>\n Although it is technically possible to enforce relational constraints across a cluster of servers, doing so would have an enormous negative impact on performance. And well, \u201cspeed and performance\u201d is the name of the game in Azure Cosmos DB, with comprehensive SLAs on availability, throughput, latency, and consistency. Reads and writes have extremely low, single-digit millisecond latency \u2013 meaning that they complete within 9 milliseconds or less in most cases. So, in order to deliver on these performance guarantees, Azure Cosmos DB simply doesn\u2019t support the concept of joins, and can\u2019t enforce relational constraints across documents.<\/p>\n With no joins and no relational constraints, the obvious question becomes: \u201cIs Azure Cosmos DB suitable for relational workloads?\u201d And the answer is, yes, of course it is. Otherwise, the post would end right here.<\/p>\n And when you think about it, most real-world use cases are<\/em> relational. But because Azure Cosmos DB is horizontally scalable and non-relational, we need to use different techniques to materialize relationships between entities. And this means a whole new approach to designing your data model. In some cases, the new methods are radically different, and run contrary to best practices that some of us have been living by for decades.<\/p>\n Fortunately, while it is very different, it\u2019s not really very difficult. It\u2019s just that, again, you need to think about things differently when designing your data model for Azure Cosmos DB.<\/p>\n Our sample database is for an e-commerce web site that we\u2019re calling WebStore. Here is the relational data model for WebStore in SQL Server:<\/p>\n This data model is relatively small, but still representative of a typical production model. It has one-to-many relationships, like the ones from Customer to CustomerAddress and SalesOrder. There is also a one-to-one relationship from Customer to CustomerPassword, and the ProductTags table implements a many-to-many relationship between Product and ProductTag.<\/p>\n It\u2019s very natural at first to think of a container in Azure Cosmos DB like a table. Your first instinct may be to say, OK, we have nine tables in our data model, let\u2019s create nine containers in Azure Cosmos DB.<\/p>\n Now you can certainly do this, but again, this would be the worst possible design, being horizontally scalable and non-relational. Azure Cosmos DB will expose no way to join documents, or to enforce relational constraints between them. Therefore, this approach will not only perform poorly, but it will be very difficult to maintain and program against.<\/p>\n What\u2019s the answer? Let\u2019s get there one step at a time.<\/p>\n Let\u2019s start with customers and their related entities. JSON is hierarchical, so we don\u2019t need separate documents for every type. So now think about the distinction between one-to-many and one-to-few<\/em>. It\u2019s reasonable to impose an upper limit on the number of addresses a customer can have, and there\u2019s only one password per customer, so we could combine all of those into a single document:<\/p>\n This has immediately solved the problem of joining between customers and their addresses, and passwords, because that\u2019s all \u201cpre-joined\u201d by embedding the one-to-few relationship for addresses as a nested array, and the one-to-one relationship for the password as an embedded object. Simply by embedding, we\u2019ve reduced three relational tables to a single customer document.<\/p>\n On the other hand, we would certainly not<\/em> want an upper limit on the number of sales orders per customer \u2013 ideally, that is unbounded (while the maximum document size is 2 MB). The orders will be stored in separate documents, referenced by customer ID.<\/p>\n The rules for when to embed and when to reference are simple, as we\u2019re demonstrating. One-to-few and one-to-one relationships often benefit from embedding, while one-to-many (particularly unbounded) and many-to-many relationships require that you reference. Embedding is also useful when all the entities are typically queried and\/or updated together (for example, a customer profile with addresses and password), while it\u2019s usually better to separate entities that are most often queried and updated separately (such as individual sales orders).<\/p>\n The next \u2013 and arguably most important \u2013 step is to choose a partition key for the customer document. Making the right choice requires that you understand how partitioning works.<\/p>\n When you create a container, you supply a partition key. This is some value in your documents that Azure Cosmos DB groups documents together by in logical<\/em> partitions. Each server in the cluster is a physical<\/em> partition that can host any number of logical partitions, each of which in turn stores any number of documents with the same partition key value. Again, we don\u2019t think about the physical partitions, we\u2019re concerned primarily with the logical partitions that are based on the partition key that we select.<\/p>\n All the documents in a logical partition will always be stored on the same physical partition; a logical partition will never be spread across multiple servers in the cluster. Ideally, therefore, you want to choose a partition key whose value will be known for most of your typical queries. When the partition key is known, then Azure Cosmos DB can route your query directly to the physical partition where it knows all the documents that can possibly satisfy the query are stored. This is called a single-partition query.<\/p>\n If the partition key is not known, then it\u2019s a cross-partition query (also often called a fan-out query). In this case, Azure Cosmos DB needs to visit every physical partition and aggregate their results into a single resultset for the query. This is fine for occasional queries, but adds unacceptable overhead for common queries in a heavy workload.<\/p>\n You also want a partition key that results in a uniform distribution of both storage and throughput. A logical partition can\u2019t exceed 20 GB, but regardless, you don\u2019t want some logical partitions to be huge and others very small. And from a throughput perspective, you don\u2019t want some logical partitions to be heavily accessed for reads and writes, and not others. These \u201chot partition\u201d situations should always be avoided.<\/p>\n With this understanding, we can select a partition key for the customer documents that we\u2019ll store in a customer container. The question is always the same: \u201cWhat\u2019s the most common query?\u201d For customers, we most often want to query a customer by its ID, like so:<\/p>\n SELECT * FROM c WHERE c.id = ‘<custId>’<\/p>\n In this case, we want to choose the id property itself as the partition key. This means you\u2019ll get only one document in every logical partition, which is fine. It\u2019s desirable to use a partition key that yields a large spectrum of distinct values. You may have thousands of logical partitions for thousands of customers, but with only one document each, you will achieve a highly uniform distribution across the physical partitions.<\/p>\n We\u2019ll take a very different approach for product categories. Users visiting the website will typically want to view the complete list of product categories. Then, they\u2019ll want to query for all the product that belong to a category that interests them, which is essentially a query on the product category container with no WHERE clause. The problem though, is that would be a cross-partition query, and we want to get all our category documents using a single-partition query.<\/p>\n The trick here is to add another property called type to each product category document, and set its value to \u201ccategory\u201d in every document. Then we can partition the product category container on the type property. This would store all the category documents in a single logical partition, and the following query could retrieve them as a single-partition query:<\/p>\n SELECT * FROM c WHERE c.type = ‘category’<\/p>\n This same is true of tags; users will typically want a full list of tags and then drill to view the products associated with interesting tags. This is a typical pattern for short lookup lists that are often retrieved all at once. So that would be another container for product tags, partitioned on a type property where all the documents have the same value \u201ctag\u201d in that property, and then queried with:<\/p>\n SELECT * FROM c WHERE c.type = ‘tag’<\/p>\n Now for the many-to-many relationship between products and tags. This can be modeled by embedding an array of IDs on one side or the other; we could either store a list of tag IDs in each product, or a list of product IDs in each tag. Since there will be fewer products per tag than tags per product, we\u2019ll store tag IDs in each product document, like so:<\/p>\n Once the user chooses a category, the next typical query would be to retrieve all the products in a given category by category ID, like so:<\/p>\n SELECT * FROM c WHERE c.categoryId = ‘<catId>’<\/p>\n To make this a single-partition query, we want to partition the product contain on the product category ID, and that will store all the products for the same category in the same logical partition.<\/p>\n Now we have a new challenge, because product documents hold just the category ID and an array of tag IDs \u2013 it doesn\u2019t have the category and tag names<\/em> themselves. And we already know that Azure Cosmos DB won\u2019t join related documents together for us. So, if we want to display the category and tag names on the web page \u2013 which we do \u2013 then we need to run additional queries to get that information. First, we need to query the product category container to get the category name, and then \u2013 for each product in the category \u2013 we need to query the product tag container to get all the tag names.<\/p>\n We definitely need to avoid this, and we\u2019ll solve the problem using denormalization<\/em>. And that just means that \u2013 unlike in a normalized data model \u2013 we will duplicate information as necessary in order to make it more readily available for queries that need it. That means that we\u2019ll store a copy<\/em> of the category name, and copies<\/em> of the tag names, in each related product document:<\/p>\n Now we have everything we need to display about a product self-contained inside a single product document. And that will work great, until of course, there\u2019s a category name or tag name is changed. Because now we need a way to cascade that name change to all the related copies in order to keep our data consistent.<\/p>\n This is a perfect situation for the Azure Cosmos DB change feed, which is a persistent log of all changes made to any container. By subscribing to the change feed on the category and tag containers, we can respond to updates and then propagate the change out to all related product documents so that everything remains in sync.<\/p>\n This can be achieved with a minimal amount of code, and implemented out-of-band with the main application by deploying the change feed code to run as an Azure function:<\/p>\n Any change to a category or tag name triggers and Azure function to update all related product documents. This lets us maintain a denormalized model that\u2019s optimized to retrieve all relevant information about a product with one single-partition query.<\/p>\n The last part of our schema are the customer orders and order details. First, we\u2019ll embed the details into each order as a single document for the sales order container, because that\u2019s another one-to-few relationship between entities that are typically retrieved and updated together.<\/p>\n It will be very common for customers to retrieve their orders using the following query:<\/p>\n SELECT * FROM c WHERE c.customerId = ‘<custId>’<\/p>\n That makes the customer ID the best choice for the partition key. But before we jump to create another container for sales orders, remember that we\u2019re also partitioning customers on the customer ID in the customer container. And unlike a relational database where tables have defined schemas, Azure Cosmos DB lets you mix different types of documents in the same container. And it makes sense to do that when those different types share the same partition key.<\/p>\n We\u2019ll combine customer and sales order documents in the same customer container, which will require just a minor tweak to the customer document. We\u2019ll need to add a customerId property to hold a copy of the customer ID in the id property. Then we can partition on customerId which will be present in both document types:<\/p>\n Notice that we\u2019ve also added a type property to distinguish between the two types of documents. So now, there is still only one customer document per logical partition, but each logical partition also includes the related orders for that customer. And this kind of gets us our joins back, because now we can retrieve a customer and all their related orders with the following single-partition query:<\/p>\n SELECT * FROM c WHERE c.id = ‘<custId>’<\/p>\n Let\u2019s wrap up with one more query to retrieve our top customers; essentially, a list of customers sorted descending by order count. In the relational world, we would just run a SELECT COUNT(*) on the Order table with a GROUP BY on the customer ID, and then sort descending on that count.<\/p>\n But in Azure Cosmos DB, the answer is once again to denormalize. We\u2019ll just add a salesOrderCount<\/em> property to each customer document. Then our query becomes as simple as:<\/p>\n SELECT * FROM c WHERE c.type = ‘customer’ ORDER BY c.salesOrderCount DESC<\/p>\n Of course, we need to keep that salesOrderCount property in sync; each time we create a new sales order document, we also need to increment the salesOrderCount property in the related customer document. We could use the change feed like before, but stored procedures are a better choice when your updates are contained to a single logical partition.<\/p>\n In this case, the new sales order document is being written to the same logical partition as the related customer document. We can write a stored procedure in JavaScript that runs within the Azure Cosmos DB service which creates the new sales order document and updates the customer document with the incremented sales order count.<\/p>\n The big advantage here is that stored procedures in Azure Cosmos DB run as a transaction that succeeds or fails as a whole. Both write operations will need to complete successfully or they both roll back. This guarantees consistency between the salesOrderCount property in the customer document and the true number of related sales order documents in the same logical partition.<\/p>\n One last thing to mention is that this is a cross-partition query, unlike of our previous examples, which were all single-partition queries. Remember again that cross-partition queries aren\u2019t necessarily evil, as long as they aren\u2019t very common. In our case, this last query won\u2019t run routinely on the website; it\u2019s more like a \u201cback office\u201d query that an executive runs every now and again to find the top customers.<\/p>\n This post has walked you through the steps to refactor a relational data model as non-relational for Azure Cosmos DB. We collapsed multiple entities by embedding, and we support denormalization through the use of the change feed and stored procedures.<\/p>\n We also combined customer and sales order documents in the same container, because they are both partitioned on the same value (customer ID). To wrap up the design, we can also combine the product category and product tag documents in a single \u201cproduct metadata\u201d container, since they are both partitioned on the same type property.<\/p>\n That brings us to our final design:<\/p>\n Using a combination of non-relational modeling techniques, we\u2019ve reduced nine tables into just three containers, where majority of queries run by the application are all scoped to a single logical partition.<\/p>\n <\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" As a NoSQL database, Azure Cosmos DB requires you think about data modeling in new ways. In this post, we\u2019ll use a familiar real-world relational data model and refactor it as a non-relational data model for Azure Cosmos DB.<\/p>\n","protected":false},"author":32777,"featured_media":1514,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[14,1611],"tags":[1736,1737],"class_list":["post-1504","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-core-sql-api","category-data-architecture","tag-data-modeling","tag-partitioning"],"acf":[],"blog_post_summary":" As a NoSQL database, Azure Cosmos DB requires you think about data modeling in new ways. In this post, we\u2019ll use a familiar real-world relational data model and refactor it as a non-relational data model for Azure Cosmos DB.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/1504","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/users\/32777"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/comments?post=1504"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/1504\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media\/1514"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media?parent=1504"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/categories?post=1504"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/tags?post=1504"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"multiple](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM02-300x45.png\")
<\/p>\n![\"A](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM03-300x92.png\")
<\/p>\nSuitable for relational workloads?<\/h3>\n
WebStore relational model<\/h3>\n
![\"Example](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM04-300x252.png\")
<\/p>\nContainer per table?<\/h3>\n
Embed vs. reference<\/h3>\n
![\"Example](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM05-168x300.png\")
<\/p>\nUnderstanding partitioning<\/h3>\n
Choosing a partition key<\/h3>\n
Many-to-many relationships<\/h3>\n
![\"Example](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM06-258x300.png\")
<\/p>\nIntroduction denormalization<\/h3>\n
![\"Example](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM07-177x300.png\")
<\/p>\nDenormalizing with the change feed<\/h3>\n
![\"image](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM08-300x107.png\")
<\/p>\nCombining different types<\/h3>\n
![\"A](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM09-300x124.png\")
<\/p>\nDenormalizing with a stored procedure<\/h3>\n
Summary<\/h3>\n
![\"three](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/06\/DM10-300x146.png\")
<\/p>\n