What are the front-end data modeling best practices for translating complex nested SQL associations into manageable services?

Question

I'm reaching out to gain perspective on possible solutions to this problem. I'll be using Angular and Rails, but really this problem is a bit more abstract and doesn't need to be answered in the context of these frameworks.

What are the best practices for managing complex nested SQL associations on the front-end?

Let's say you have posts and comments and comments are nested under posts. You send your posts to the front-end as JSON with comments nested under them. Now you can display them listed under each post, great. But then questions arise:

1. What if you want to display recent comments as well? Your comment service would need to have comments in a normalized collection or gain access to them in a fashion that allows them to be sorted by date.

   • Does this mean you make a separate API call for comments sorted by date? This would duplicate comments on the front-end and require you to update them in two places instead of one (one for the posts and one for the comment, assuming comments can be edited or updated).
   • Do you implement some kind of front-end data normalization? This meaning you have a caching layer that holds the nested data and then you distribute the individual resources to their corresponding service?

2. What if you have data that has varying levels of nesting? Continuing with the posts and comments example. What if your comments can be replied to up until a level of 10?

   • How does this effect your data model if you've made separate API calls for posts and comments?
   • How does this effect your caching layer if you choose that approach?

3. What if we're not just talking about posts? What if you can comment on photos and other resources?

   • How does this effect the two options for data-modeling patterns above?

Breaking from the example, what if we were talking about recursive relationships between friended users?

---

My initial thoughts and hypothetical solution

My initial thought and how I'd attack this is with a caching layer and normalize the data such that:

1. The caching layer handles any normalization necessary
2. The caching layer holds ONE canonical representation of each record
3. The services communicate with the caching layer to perform CRUD actions
4. The services generally don't care nor do they need to know how nested/complex the data model is, by the time the data reaches the services it is normalized

Recursive relationships would need to be finite at some point, you can't just continue nesting forever.

---

This all of course sounds great, but I see lots of potential pitfalls and wish to gain perspective. I'm finding it difficult to separate the abstract best practices from the concrete solutions to specific data models. I very interested to know how others have solved this problem and how they would go about solving it.

Thanks!

Answer 1

I assume you will use restful apis, attention I don't know rails but I will suggest you some general practices that you might consider

Let's say you have one page that shows 10 posts and their 10 comments sorted by date, make this response possible in one api call

There is one more page that shows only 5 posts and no comments use the same api end-point

Make this possible with some query parameters.

Try to optimize your response as much as you can.

You can have multiple response type in one end-point, in any programming languages, if we talking about APIs thats how I do the job.

If you query takes much time, and that query runs serveral times then of course you need to cache but talking about 10 posts in each api call doesn't need caching. It should not hard on database.

For nesting problem you can have a mechanism to make it possible i.e I will fetch 10 posts and their all comments, I can send a query parameter that I want to include all comments of each post

like bar.com/api/v1/posts?include=comments

if I need only some customized data for their comments, I should be able to implement some custom include.

like bar.com/api/v1/posts?include=recent_comments

You API layer, should first match with your custom include if not found go on relation of the resources

for deeper references, like comments.publisher or recent_comments.publisher your API layer needs to know which resource are you currently working on it. You won't need this for normal include, but custom includes should describe that what model/resource they are point to that way it is possible to create endless chain

I don't know Rails, but you can make this pattern easily possible if you have a powerful ORM/ODM

Sometimes, you need to do some filtering same goes for this job too.

You can have filter query parameter and implement some custom filters

i.e bar.com/api/v1/posts?include=recent_comments&filters=favorites

or forget about everything and make something below
bar.com/api/v1/posts?transformation=PageA
this will return 10 recent posts with their 10 recent comments
bar.com/api/v1/posts?transformation=PageB
this will return only 10 recent posts
bar.com/api/v1/posts?transformation=PageC
this will return 10 recent post and their all comments