K3 showed what happens when all appointments of an office live in one document: It grows to the limit of 2 MB and is then dead. The repair was one item per appointment. This lesson turns the repair into a procedure that you apply to every relationship of Kladde.Verlauf: access patterns first, then the shape.

Prior knowledge. The calculation for the office document and the limits are in "K3, lesson 3.6.4" and are not repeated. How the partition key helps determine the answer follows in lesson 1.4.

What was run. The calculation program of the lesson was compiled with .NET 10 and ran without an account (it needs only System.Text.Json). The queries in SQL are not run against Azure. Numbers are assumptions of the task; the sizes in the outputs are measured on the sample text. The output shows German number formats (period as thousands separator); with an English culture there are commas.

Access patterns first

A relational model asks: Which things exist, how are they connected? A document model first asks: What does the application read and write, how often, with which key? For Kladde.Verlauf (F-03):

No. Access Kind Key values known
Z1 calendar of an office for one month read, often office, month
Z2 open one appointment read, often office, month, id
Z3 activities of an office on one day read office, day
Z4 history of a support conversation read, often office, person, conversation
Z5 show the latest messages of a conversation read, very often office, person, conversation
Z6 append a new message write, very often office, person, conversation

Z1 to Z6 know the key; that is why they are the keys in F-03. An access without key values (for example "all appointments of a contact person in all offices") would be a query across all partitions and belongs in an analysis, not in everyday operation (lesson 1.4).

Embed or reference

Learn states the rules like this, and they match the access patterns:

  • Embed when the relationship is containing, one to few, the data rarely changes, does not grow without bound and is mostly read together. Result: one read, one write.
  • Reference for one to many or many to many, for frequently changing or unbounded data. Result: faster writes, but more reads, because Cosmos DB joins nothing across containers (JOIN works only within one item).

Where the reference sits is decided by growth: With the publisher who has unboundedly many books, each book carries the publisher ID, not the publisher a list. For the Kladde that means: An appointment carries officeId, the office carries no list of appointments (that is the field report from K3).

Cosmos DB has no foreign keys. Whether a contactId points to anything is checked by the application (or a trigger); the reference is "weak". That is one of the reasons why the Kladde's rules stay in SQL.

Copying and upkeep of the copy

In between lies copying: You carry along part of the data of another item so that a list gets by without a second access. Learn's example: The book carries the name and picture of the authors, not just their id. The price is upkeep: If the name changes, all copies must be updated, otherwise they drift apart.

In the Appointment item (F-03), contactName is a copy of the name from the Kladde. How expensive would it be to change a name everywhere? An assumption: 400 contacts, 7,360 appointments a year (F-02, buero-nord), evenly distributed, one write of about 6 RU (assumption of the task from lesson 1.1). For one contact the program counted:

appointments of one contact in a year: 19; a rename of all copies = 114 RU

114 RU is little. The better question is therefore not "what does it cost" but "should it be done?" A past appointment is a fact: It took place under the name of that time. A copy that captures a point in time is not maintained. A copy that is meant to be always current is maintained with the change feed (lesson 2.5). You decide that per property and write it into the model.

Related are precomputed values (Learn: countOfBooks in the author). They save compute at read time and cost compute at write time; that pays off for read-heavy applications. Because one write can change several items of a partition in one transaction (lesson 2.3), you do not have to squeeze everything into one item for this.

Several item types in one container

Items of different kinds may live in one container if they are read together or changed together, provided they share the partition key. Learn advises adding a type field. For conversations (key /officeId, /userId, /conversationId, F-03) that means: one conversation item as the header, plus one message item per message, all with the same three key values, so in one logical partition. Then one query with the key reads the whole history (Z4), header and messages can be changed together, and the header carries the latest messages for Z5.

Why not all messages in the header? The program built a conversation with messages of about 230 bytes (role, point in time, one sentence of text) once embedded:

C#
foreach (var count in new[] { 10, 100, 1_000, 5_000 })
{
    var doc = new ConversationEmbedded("c-001", "conversation", "buero-nord", "u-17", [.. Enumerable.Range(0, count).Select(MessageNo)]);
    Console.WriteLine($"  {count,5} messages: {SizeOf(doc),9:N0} bytes");
}

A. embedded messages
     10 messages:     2.414 bytes
    100 messages:    23.339 bytes
   1000 messages:   232.589 bytes
   5000 messages: 1.162.589 bytes
  the limit of 2.097.152 bytes is passed at message 9.020

And as a header with the latest three messages, with one item per message alongside:

B. header plus message items
  header (5000 messages, 3 embedded): 813 bytes
  one message item:                   234 bytes

The header stays at 813 bytes no matter how long the conversation gets; each message costs only its own 234 bytes on write, not the whole conversation. That is the hybrid solution: embedded is what is small and bounded (the latest messages for display), separate items is what grows. The queries for it (not run against Azure), always with the complete key:

SQL
SELECT * FROM c WHERE c.type = 'conversation'
SELECT * FROM c WHERE c.type = 'message' ORDER BY c.at DESC OFFSET 0 LIMIT 20

Limits of this pattern: All types of a container share throughput and indexing policy, and every query must filter on type or set the key so narrowly that it hits only what is wanted.

1:n and n:m without foreign keys

Relationship Pattern Example Kladde.Verlauf
1:few, rarely changed embed participants of an appointment (a handful at most) as a list in the appointment
1:many, unbounded child carries the parent's ID appointment carries officeId and month
frequently changed data separate item, reference state of a task that changes often
n:m IDs in a list on one or both sides, instead of a join table appointment and participants

For n:m, Learn advises leaving out the join table and keeping the lists of IDs on both sides, as long as the lists stay small. A join table always costs two additional queries.

Numbers in JSON belong here too: Numbers outside IEEE 754 (double) should be stored as text, especially in partition keys, because the key can no longer be changed later.

Exercise 1

Part A, without an account. Create model.cs (Message, ConversationEmbedded, ConversationHeader, SizeOf).

  1. Find the number of messages at which the embedded version exceeds 2 MB (loop as above).
  2. Measure the header with the latest three messages at 5,000 messages, and a single message.
  3. For the four relationships decide embed, reference or copy and write the upkeep rule for each: office and appointments; appointment and contact name; conversation and messages; activity (events) and appointment (title).

Part B, with a test account (not run against Azure): Create a header and three messages in conversations and read the history with a query with the full key. Cleaning up: delete the items or the database of the test.

How to check it: Part A shows the numbers of the solution (with a different sample text, different values; the procedure counts).

Solution 1
  1. With the sample text the limit is at message 9,020 (2,097,152 bytes), so at about 232 bytes per message. A lively conversation never reaches that, a memory over years does (lesson 4.3).
  2. 813 bytes for the header, 234 bytes per message.
  3. Office and appointments: reference (the appointment carries officeId), unbounded. Appointment and contact name: copy as a snapshot; past appointments keep the name of that time (fact), future ones pick up the new name only if the business side demands it (change feed, lesson 2.5); the cost would be 114 RU for 19 appointments. Conversation and messages: header with the latest three messages (embedded, bounded) and one item per message in the same partition. Activity and appointment: the title is copied and never maintained, because an activity describes what happened at a point in time; the 90 days of expiry (F-03) clear it away.

Part B stays unchecked. Open remains whether the Kladde reports renames to Kladde.Verlauf at all; the outbox decides that (K4, lesson 4.6.2).

What you can do now

  • You derive a model from access patterns with known key values.
  • You decide between embedding, referencing and copying by growth and rate of change.
  • You estimate the upkeep of a copy in RU and first ask whether it is needed.
  • You keep header and children in one partition and separate the types with type.
  • You map n:m with lists of IDs and know that the application checks the references.

Further reading