none
Table design for comment response structure

    Question

  • Hi everyone,

    Using Azure tables, what would be the best structure (scalability/performance/price) for a solution to store comments?

    Main Comment 1
     Response 1
     Response 2
     Response N
    Main Comment 2
     Response 1
     Response 2
     Response N
    Main Comment N

    I will like to use as less transactions as possible to retrieve let’s say 10 comments at a time with all its responses included.

    Thanks

    Tuesday, December 07, 2010 3:43 PM

Answers

  • One way to solve this is to design your web UI accordingly.  I do not know if this would satisfy your requirements, but you can just get the 10 comments back without getting their responses.  Then, have a button on each comment to ajax fetch its responses to show to the visitors.

    Amazon has reviews shown under a product, then have a link next each review which leads to its separate page to show the discussion on that review.

    • Marked as answer by Igor33 Thursday, December 09, 2010 6:30 PM
    Tuesday, December 07, 2010 7:37 PM

All replies

  • Could you give more details about your requirement?

    Scalability and Performance are achieved by having a good design on the partitions.  Basically, more partitions you have, the easier Azure can distribute data to different nodes which is good for scalability.   On the other hand, the less partitions you have, meaning more data stay together, the easier Azure can select data on your queries which is good for performance.  Thus, you want to have a balance between the two, which ultimately comes down to your biz requirement and your design.

    Tuesday, December 07, 2010 5:36 PM
  • You could probably use a unique comment ID as PartitionKey and use some Ticks for the RowKey. This way all comments, including the main one, are in the same partition. This helps query performance and also allows you to use entity group transactions if you want to delete the comments. You can then use Ticks to order the comments in either chrononological or reverse chronological order.
    Tuesday, December 07, 2010 6:32 PM
    Answerer
  • Hi Neil,

    Comments and its responses are in reference to another object, let say a product.

    So the structure really is:

    Product
      Comments
         Responses.

    I need an efficient way to pull ten comments at a time with all its responses for a given product.

    Thanks

    Tuesday, December 07, 2010 6:54 PM
  • Here is my 2 cents on a design. 

    PartitionKey: ProductID or BlogPostID (depending what your sys does)

    RowKey: DatePosted.Ticks  (returns things in chronological order)

    Type: (comment or response)

    ParentID: it's parent RowKey (if the type is response, empty if it is comment)

    This design puts all comments+responses for a product in one partition.  It should be quick to get all the comment+response for the product.  Then, you can loop through to create the hirarchical structure you want.

    Tuesday, December 07, 2010 6:58 PM
  • Thanks Ray,

    That’s what I though as well, however if a have 100 comments with 10 response each in average that’s 1000 entities; even though its only one transaction, if I want to display 10 comments at a time, that might be a waste of resources if let’s say the users goes up to page 3.

    I was hoping for a design that lets me do pagination (10 comments at a time with all its responses), but I want to avoid having to pull (take) top 10 from comments and have to issue 10 different transactions to get responses for each comment.

    Tuesday, December 07, 2010 7:14 PM
  • One way to solve this is to design your web UI accordingly.  I do not know if this would satisfy your requirements, but you can just get the 10 comments back without getting their responses.  Then, have a button on each comment to ajax fetch its responses to show to the visitors.

    Amazon has reviews shown under a product, then have a link next each review which leads to its separate page to show the discussion on that review.

    • Marked as answer by Igor33 Thursday, December 09, 2010 6:30 PM
    Tuesday, December 07, 2010 7:37 PM