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.
