how to store user mentions

I want to implement user mentions with @username like in Twitter. But, in my app, the username may change. My approach is to parse the post before saving into the database and convert all the @username to @userId. What do you think of开发者_StackOverflow中文版 this? Does anyone has any better alternative?

Store the original text, as is, and create a table of related records with the uid and username.

Example schema:

post [table]

id
text

user_mention [table]

id
post_id
user_id_mentioned
user_name_mentioned

When a post is saved, your code should go through and create all the user_mention records. You can loop through the mention table to send e-mails or whatever else you want to do.

If the user changes their user name, you now have the option of updating the post with the new username, or having the old username link to the correct user.

My rule is to never, ever modify original unstructured text before saving to the database (but do sanity check it to avoid injections and whatnot) and modify the data only on output. This means you always have the user entered data, and you never know when that will be valuable.

Maybe one day you are interested in who changed their username after being mentioned n number of times? Or some other report that you can't get because you modified unstructured data. You never know when you will want the original data and, in this case, you get the added bonus of having your text easier to work with.

Yeah, I think checking the username list at the post time, and converting them internally to a user ID makes sense. Then, whenever you display the post, translate the user ID back to the current username.

Note that this will be more difficult for non-dynamic content, such as emails sent, etc.

Also, I'd make sure that the usernames are displayed in a way that makes it clear that they're not words the OP posted, otherwise, that would give a way for users to inject text into someone else's post.

Yes I think that is good. Twitter itself doesn't just use Usernames it uses UserIDs.

It gets the tweeters user ID then looks it up to get the the actual username.

Documentation : Mentions and Lookup

Each user should have a unique ID. You should match the ID's with the username before you send it anywhere which would be visible for users.