How would I do post-specific custom names?

[JLT]

Hi there.

I have posts, post comments and users tables which are all fine in and of themselves, but I'd like users to have a unique name that's specific only to the post to hide their real username. This applies to the post itself, and the comments of the post and must remain consistent. For example, if Bill made a post with the name "BigFish", if they comment on their own post then their comments will also have that same name. Likewise, if another user comments with the name "BowlingBall", their subsequent comments will also have the same name.

Now, my first attempt was to have an intermediate table of sorts, containing the post_id, user_id and display_name. This takes the appearance of a composite primary key, which are not supported in Laravel. Laravel relationships of such nature can be achieved with a package called Compoships, which does work and given the nature of the table, their resulting query being a gigantic mess of the following is reasonable at best.

(post_id == ? AND user_id == ?) OR (post_id == ? AND user_id == ?) OR ...

However, that can quickly scale and definitely hurts. Another way to tackle it is to retrieve all display names for each post ID, but that's then retrieving unnecessary data. It's also possible to scrap the table, store the names in the posts and post_comments tables. That's as simple as checking if the commenter is the post author and use that name, or checking if they've previously commented and use that name. I'm not sure if that's ideal either.

This brings me back here, after a decade of inactivity, and I do apologise for the lengthy post. 

How would I go about achieving what I want to do in the best way possible? What would you do in this scenario?

Thanks in advance, looking forward to y'all suggestions.

[requinix]

1 hour ago, JLT said:

Now, my first attempt was to have an intermediate table of sorts, containing the post_id, user_id and display_name. This takes the appearance of a composite primary key, which are not supported in Laravel.

You don't have to make the primary key of an intermediate table be a composite of the foreign keys. It can be an auto-increment ID just like every other table.

1 hour ago, JLT said:

However, that can quickly scale and definitely hurts.

I don't get why people keep saying this...

Databases are designed to handle large tables like that. It's what they do best. It's basically their whole reason for existence.
All you need to do is put proper indexes on the right columns, or combination of columns, and the database server will handle the rest.

1 hour ago, JLT said:

How would I go about achieving what I want to do in the best way possible? What would you do in this scenario?

Missing some answers to make that decision myself. Such as:

Where are these usernames coming from? Are people choosing them themselves? What's to stop them from picking the same name in multiple places? Do you want to approach this feature as allowing users to pick names (optional anonymity) or requiring them to pick names (enforced anonymity)?
Also, how do you handle uniqueness across the site? If I pick "BigFish" in one thread, can I use it again in another thread? Can somebody else use that name in another thread? Can I enter something which is my actual username? Can I center something which is someone else's username? In precisely what domain of inputs are names unique?

Those questions are far more interesting to me than determining what database structures to use. Why? Because database design is mostly a solved problem: given a particular scenario, the requisite design is already known to the industry, and there's not really a whole lot of variability necessary beyond that.

[JLT]

1 hour ago, requinix said:

You don't have to make the primary key of an intermediate table be a composite of the foreign keys. It can be an auto-increment ID just like every other table.

Yes, it can and currently does have an ID auto-incrementing column too, but when searching for user_id + post_id combination, that auto-incrementing field isn't utilized unless it was a back and forth situation where the anonymous user is created with a null post_id if one doesn't exist with the post_id, create the post and/or comment with that anonymous user record, update that anonymous user with the appropriate post_id if needed. 

1 hour ago, requinix said:

I don't get why people keep saying this...

It's nothing more than bad habits. Seeing an incredibly huge query along with the undesirable desire for premature optimization may be what drives the thought process. I did compare compoships vs grabbing all anonymous names, but it wasn't a fair comparison. After all, I was only checking the query duration without taking into account that the latter then has to filter out names in PHP rather than MySQL.

1 hour ago, requinix said:

Where are these usernames coming from? Are people choosing them themselves? What's to stop them from picking the same name in multiple places? Do you want to approach this feature as allowing users to pick names (optional anonymity) or requiring them to pick names (enforced anonymity)?
Also, how do you handle uniqueness across the site? If I pick "BigFish" in one thread, can I use it again in another thread? Can somebody else use that name in another thread? Can I enter something which is my actual username? Can I center something which is someone else's username? In precisely what domain of inputs are names unique?

The anonymity is optional, but what they chose will persist with the post. For instance, I chose to be anonymous for the post either when creating the post or making a comment, I cannot then comment with my real username in that post. The optional aspect seems simple enough to me, use the anonymous name if found, otherwise use the users username. If the auto-incrementing ID of the anonymous user is stored with the post / comment, it could check if the ID is present first rather than searching a table for what doesn't exist? Not certain.

As for where the names come from, currently I'm just using a faker but the plan is to have a list of adjectives and nouns, and it'll pick one from each. For the sake of adding and removing words on the fly, having them stored in the database would be most ideal. The user is unable to type their own name, two users cannot have the same name in the same post, but can be given the same name in another post. BigFish from one post is completely separate to BigFish in another post.

I think that answers your questions?

Edited May 7, 2023 by JLT

[kicken]

10 hours ago, JLT said:

This takes the appearance of a composite primary key, which are not supported in Laravel.

Sounds to me like you have the correct solution as far as the DB is concerned, it's just your framework choice that is the issue.  I use Symfony/Doctrine and it could handle this setup no problem.

If you can't do what you want using Laravel's setup, you may need to just resort to doing some manual queries.  I don't use laravel so can't say for certain if this really is a limitation or how to work around it.

 

[requinix]

Yeah, I think the post+user+display name table is the answer as well.
A less traditional answer would be to go pseudo-NoSQL and store a JSON blob of user ID/display name mappings in the post; it comes with minor upsides and minor downsides, and probably isn't worth it.

There are still some holes in here to potentially address, though. Like, if anonymized names are optional then there are two input spaces of (actual usernames) and (generated display names) and any overlap between them could be a problem.
But they don't really affect database structure, I don't think.