CODEWITHSUNDEEP
postgresql
Ginden
Ginden

Reputation: 5316

Which nested data structure should I use for posts with parents?

I have table Posts with fields id, author_id, message, parent_id. parent_id can be null or id of corresponding parent post.

I have also table Votes that contains fields id, item_id (foreign key referencing to Posts.id).

Which of many ways to store hierarchical data should I use for this example?

I want to return response from server similar to this:

[{
    user: {/* author-data */},
    text: 'some string',
    parent_id: null,
    comments: [/* list of comments, each including it's own list of votes*/],
    votes: [/* list of votes*/]
}]

Data will be almost never updated, quite often inserted and heavily read.

Upvotes: 1

Views: 88

Answers (1)

Wolph
Wolph

Reputation: 80031

Just store it the "normal" way, using a foreign key from parent_id to id.

After that query it with a recursive query: http://www.postgresql.org/docs/9.3/static/queries-with.html

If you ever get performance issues with this, you can just add a simple caching layer.

Upvotes: 3

Related Questions