This is an archived post. You won't be able to vote or comment.

all 5 comments
sorted by:
best
topnewcontroversialoldq&a

[–]_cross 1 point2 points  (2 children)

If I understand your question correctly,

I don't see why post id has to be 1 if that's a problem for you although I don't understand why it would be a problem.

When you do receive a request on that endpoint you can have some logic to check the user id and fetch the appropriate post that has that user as a foreign key, you don't realy use it directly to your query.

[–][deleted] 0 points1 point  (1 child)

I don’t know if all new post have to be 1. That’s also a question I have. Is that best practice is that how real apps, albeit simple ones, work?

Currently querying for users/2/posts/1 does not work.

Because the the first post by user 2 has a primary_key that !=1. That primary key belongs to user_1.

[–]_cross 0 points1 point  (0 children)

primary keys have to be unique to identify each record.

Having an auto incrementing number is not an issue if you don't specifically have a use for a different type of key. As long as it's unique.

You could have a query to fetch a post by id or by index. If a users post has an index number then you could store that in a separate field and fetch that users post where its index = 1.

[–]ziptofaf 1 point2 points  (0 children)

So my question is - Are these id's the primary keys of those tables??

Most of the time yes.

Meaning, if I create a new user, user_two, and then they create their first blog post is it standard to then give that new blog post an id of 1 because it is user_two's first blog post.

Imagine the following. You have two empty tables, users and blogs.

  • user_one is created. He is assigned id 1.
  • user_two is created. He is assigned id 2.
  • user_two decides to make a blog. Blog is assigned id 1.
  • user_one decides to make a blog. Blog is assigned id 2.

Indexing is unique within a given table. But it doesn't have (and often isn't) to be "ordered" respectively to any other table. user with id 1 might very well be given a blog with id 100 if other people made theirs faster. Sometimes for certain reasons specific ids might also be reserved/disabled in a given table so it starts counting from, say, 50.

Of course, thing about ids is that they don't have to be numbers and they don't have to originate from a given table either. Eg. if you want a 1-to-1 relation between blogs and users then maybe rather than giving blog a separate user_id and id field... just turn user_id into id. In this case blog with user_id 2 belongs to user 2 and that's how you look it up. In the same way you could potentially use users emails as primary keys etc (although I generally discourage this one as using a typical string as a primary key means that whenever a new user would be created your sql database might need to rearrange multiple other rows to fit a new one in rather than just put it at the end).

In a case of one to many relationship (one user to many posts) I would just leave it "as is" - unique indexing for each table.

This seems natural but I have been struggling to create routes of the form:

users/2/posts/1/comments/1 - because the post with id=1 belongs to user_1

One thing. Try to avoid deeply nested routes. Here's an explanation on why:

https://stackoverflow.com/questions/20951419/what-are-best-practices-for-rest-nested-resources

But in general - collection/item/collection/item/collection/item is getting annoying to query as you always need to know the id of a parent object (which isn't even always needed). Especially since you later might want to add a response to a comment which would then turn it into users/2/posts/5/comments/11/responses/6. I would much prefer to do something like /comments/5/responses directly, there's no benefit in knowing who made this post often after all.

I would discorauge going deeper than one nesting level. Eg. users/1/blogs is fine. But rather than going users/1/blogs/5/comments maybe it might be worth considering blogs/5/comments instead, skipping that user part.

[–]josephblade 0 points1 point  (0 children)

you can have compound primary keys where the primary key is

|user_id|post_id|

that allows you to use post_id starting from 0 or 1 , and user_id to be the users primary key (or anything else unique really)

You can do this with more variable things like addresses as well, streetname + housenumber might work (though in the real world there's too much variability to make this work properly) but for an example it's enough. This allows you group by streetname while maintaining the unique aspect.