Design A: Make server do more work

Given a client url in such format www.mywebsite.com/profile/[username], its page generates a number of components that further requests data from the server to fill the page with content. Some of these components relies on this [username] as part of its parameter for example api/posts?filterBy=[username] returns posts for the page, and api/users/[username]/comments returns profile comments for the page.

Switching our perspective to the database backend, username is not actually a primary key in the user table but one with a unique index constraint. Instead, I have a userid as a surrogate primary key with uuid type. A lot of table references userid as FK.

As you can imagine above, with such structure I now forced myself to make sub-queries on the database end to every entity that has a userid as FK. Like given username, I have to find corresponding userid, only then I can filter something such as post table by where post.userid = userid This design makes a tradeoff for usability on client side. As a novice, I don't know whether this cost is large or small as it scale. But since it affects any tables that require filter by userId, which is likely used heavily, I imagine it is a bad design.

Design B: Slow-down user pageload instead

The alternative strategy I thought of is to create a once and for all subsequent request. On page load of www.mywebsite.com/profile/[username], the client first calls the endpoint api/users/[username] to get user info including attribute of userid. This userid trickles to the components to make calls to api/posts?filterBy=[userId] or api/users/[userId]/comments - emphasis on the [userid] is no longer [username] - which makes the backend less workload without the need to make subquery to get userId.

In scenario A, we tax the backend for a blanket effect of subqueries in order to provide abstraction one front end. While scenario B for the same abstraction, we instead are blocking and gated by the first query to carry through to make the subsequent queries (Is this bad design hindering loading speeds).

Now the whole point of making www.mywebsite.com/profile/[username] not [userid] is for a user experience perspective. If i could make www.mywebsite.com/profile/[userId] then certainly it would make things easier, but that is not an option.

I assume the two given scenarios are trade-offs, but I hope theres is a third or forth strategy that I've missed where someone can enlighten me.

now...there is a 3rd design I can conjure, I may implement a coarse grain request like /profile/[username]?includes='userid, posts, comments' that will return data to hydrate the page's components. This will give the client userId and some component data, while specific endpoints can remain and engage if needed further filtering i.e. api/posts?filterBy=[userId]&orderBy=[views]. This seem like a workable solution, however, the lost of separation of concern will increase chance of bugs and maintainability as the application scale with more 'components' and endpoints.

Additional Notes: I'm designing frontend and backend for my application with the following stack: SPA/Next.js, Node.js + Postgresql

Edit: I just thought of a 4th hybrid approach:

Introduce a query parameter for id: as such https://mywebsite.com/profile/[username]?id=[userId]

if exists, my components will use that id. id exists by way of hyperlinks. i.e. user navigating a profile icon from explore page -> user profile. With that my endpoints api/posts/[userid] can work.

else if, id param has no value; return to design B, fetch api/users/[username] get userid, append userid to id param on url.

1 Answer 1


If your unique index has only the username column (not compose index), a query to username should be very efficient (at least when compared with query to your other components). If you really need to squeeze any performance, you may keep userid somewhere for reuse (as in Design B).

In your designs, you considered to keep userid in URL only. Some other possible location are HTTP request body, cookie, app server session variable...

Note that if you keep userid and username in URL, end user can easily change one of them to make it inconsistent (keeping in other location make changing them a bit more difficult). So you need to be decide which to believe. For example, in the URL for this question, stackexchange believe 308264 and basically discard anything after the slash.

  • You make a very good point with the consistency and targeting between userid and username on the one URL. I think I would prioritize userid in my case. The reason i choose url is that if user hard refreshes, the url will remain as the truth, and for most cases it is fresh and it wont alter - and even if it does, the inconsistency can auto replace based on fetched data from id param (if exists), if it fails or doesn't exists then on username, otherwise is a 404 link. ...
    – Qi luo
    Mar 4, 2022 at 4:05
  • ... for cookies, then it would mean a new cookie will be made for every profile visited. for server session variable, then there could be a longer duration chance for stale match between username-userid reference if user changes it. it also adds another step to ask client be redirected. may I ask how does http request body help ? thank you for the opinion tho , lol made me tinker a bit more.
    – Qi luo
    Mar 4, 2022 at 4:07
  • edit: actually i guess staleness would only be so if i treat session as a cache rather than updating with every request.
    – Qi luo
    Mar 4, 2022 at 4:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.