Databases
StackOverflow2013
Postsdbo
POAzure table storage - pattern for parent-child (self referencing schema)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAzure table storage - pattern for parent-child (self referencing schema)
    text
    copied!<p>Using <code>Windows Azure Table Storage</code> (WATS) and trying to update the app to use Azure. I've read many articles, and am not sure on the best approach for this, that is parent to child in a self referencing model. </p> <p>ie a single parent message could have many child sub-messages. In a DB model, it would be a self referencing table. </p> <p>How would I best structure this for <code>WATS</code> so that when I make a query "Give me 10 parent records", it will also return all the child-messages belonging to the parent...</p> <p>The entity of the message / submessage as below. I've tried to define the PK and RK as below:</p> <pre><code>public class TextCacheEntity : AzureTableEntity // custom table inherits AzureTableEntity { public override void GenerateKeys() { PartitionKey = string.Format("{0}_{1}_{2}", MessageType, AccountId.PadThis(), ParentMessageId ); RowKey = string.Format("{0}_{1}", DateOfMessage.Ticks.ReverseTicks(), MessageId); } public string MessageType { get; set; } public int AccountId { get; set; } public DateTime DateOfMessage { get; set; } public string MessageId { get; set; } public string ParentMessageId { get; set; } // other properties... } </code></pre> <p>I thought of an implementation so the child messages store the parentMessagesId, and the parent parentMessageId would be empty. </p> <p>The pattern would then be</p> <ol> <li><p>Get the parent messages </p> <pre><code>.Where(o =&gt; o.ParititionKey == "Parent_000000000000001_").Take(10) </code></pre></li> <li><p>Get the child messages. Iterate through all the parent messages and using a parallel for loop</p> <pre><code>.Where(o =&gt; o.ParititionKey == "Child_000000000000001_" + parentMessageId) </code></pre></li> </ol> <p>But the problem is that this will result in 11 queries ! </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload