locked
What is the best way to Design --> Post and Comment Structure using Azure Table Storage ? RRS feed

  • General discussion

  • Ok, I'm working on a solution where Users can comment on people's posts. In traditional DB I can have three tables (User, Post and Comment) and associate them with PK and FK and things would work.

    Now, in Azure world where Joins are not possible, What is the best way to achieve the best scenario ? I can have Post and Comment on that post in the same table sharing the same partition key which would work, but how to get the data of  the User table which contains the name of the person who posted this ?

    Any ideas ?

    Sunday, January 2, 2011 9:11 PM

All replies

  • In a post/comment situation, where it is not the end of the world if data is outdated, I would be tempted to denormalize the name into the post/comment table.

    Sunday, January 2, 2011 9:47 PM
    Answerer
  • Now, how would ur linq query would look like if you have two tables ?

    Post Table:

    PartitionKey,RowKey,Timestamp_DateTime,CreatedDate_DateTime,Message,ModifiedDate_DateTime,Subject,Post

    h@t.com,h@t.com-03a9182e-d52b-4347-9c8a-666beafa5ee7,1/2

    Comments Table:

    PartitionKey,RowKey,Timestamp_DateTime,Comment,CommentMadeBy,CreatedDate_DateTime,ModifiedDate_DateTime
    h@t.com,h@t.com-03a9182e-d52b-4347-9c8a-666beafa5ee7*d51c279b-081c-4ac7-9f4c-9cb6295fc6b4,1/2/2011 7:57:11 PM,comment made by me,h@t1.com,1/2/2011 12:00:00 AM,1/2/2011 12:00:00 AM

    Sunday, January 2, 2011 10:20 PM
  • Now, how would ur linq query would look like if you have two tables?

    You would need to do separate queries to retrieve data from both tables. Also, when you denormalize you become responsible for relational integrity.

    Sunday, January 2, 2011 11:24 PM
    Answerer
  • Relational Integrity is one aspect. The big thing I'm concerned about is the performance and every transaction is charged in Azure, so would like to reduce the transactions as much as possible.

    In my case, some Posts can have comments some won't and I don't know this before hand until or unless I do both that queries and find out.

    Say I start duplicating data in every table, where to draw the line ?

    Sunday, January 2, 2011 11:37 PM
  • The post/comment model has come up a few times in the Azure forum. I believe the general pattern has been to put posts and comments in the same table and use the same PartitionKey for a post and all its comments. This way entity group transactions can be used for updates to the post and its associated comments while the likelihood of receiving continuation tokens with queries is diminished because a post and its comments are in the same partition. There are obvious modifications to this such as using the same PartitionKey for all posts by a user and using a composire RowKey to distinguish individual posts and their comments.

    Schema design in a relational database is a static function of the data because the emphasis is on relational integrity rather than operational efficiency. In Azure Tables, the relative cost of storage and operations makes schema design more of a dynamic function of the data because the cost of operations can be higher than the cost of storage. Consequently, when designing the schema you really need to consider the likely workload along with the data.

    Sunday, January 2, 2011 11:58 PM
    Answerer