But now then if you want to expose a detail page for that user the id for identifying that page has to include all this potentially personal information about them?
You don’t have to use the PK as the URL slug. Even if you want to route that way, you can have an internal ID and external ID. This is one way to use something random like a UUIDv4 for display without incurring the costs (at least, some of them) of having it as a PK.
And then if you want to list other entities to that user you will have to start mapping the external id and foreign relationships every time to external users?
And also if you are doing exception logging, for ids/primary keys there's higher odds of them being logged out, including your own logs and also external platforms.
It feels like having primary key set up like this just will complicate everything unnecessarily for the future including many edge cases that you don't foresee.
Just have the main ID not have any meaning.
It shouldn't contain information about the date, it shouldn't be auto increment, it should really be just random.
The solution I outlined is the one GitLab and PlanetScale both use internally, so it has been tested at scale and works well, for both Postgres (the former) and MySQL (the latter).
> It shouldn't contain information about the date, it shouldn't be auto increment, it should really be just random.
That’s a great way to tank performance. You want your PK to be k-sortable.
> And then if you want to list other entities to that user you will have to start mapping the external id and foreign relationships every time to external users?
If we're talking about relational database engines, that's what they do, relate things. One join statement is much the same as another.
e.g. instead of mysocialmedia.com/users/2374927
you would be showing
mysocialmedia.com/users/email@example.com-2024-06-05-mysocialmedia.com
Then exposing a lot of information that you may have not wanted to expose.