Best way to store comments with mentions (@FirstName) in database

Question

Was wondering what is the best way to store comments in a database (sql) that allows mentioning of other users by a non-unique natural name?

E.g. This is a comment for @John.

The client application would also need to detect and link to corresponding user profile if his/her name was clicked.

My initial thought was to replace the user's first name with the id and some metadata and store that in the DB: This is a comment for <John_51/> where 51 is the id of that user. Clients can then parse that and display the appropriate user name and profile links.

Is this a good approach?

Some background:

What I would like to achieve is similar to facebook posts where it allows you to 'tag' a user by just mentioning their name (not the unique username) in a post. It doesn't have to be as complex as facebook as what I need it for isn't for a post, but just comments (which can only be text, as opposed to posts which could be text mixed with videos/images/etc).

The solution would affect the database side (how the comments are stored) and also the client side (how the comments are parsed and displayed to the user). The clients are mobile apps for iOS and Android but also looking to expand to a web application as well.

I don't think the language matters as much but for completeness sake, I'm using Python's Flask with SQLAlchemy frameworks on the backend.

Current DB schema for comments

COMMENT TABLE:
id         (<PK>)
post_id    (id of the post that the comment is for: <FK on a post object>)
author_id  (id of the creator of the comment: <FK on a user object>)
text       (comment text: <String>)
timestamp  (comment date: <Date>)

Edit:

I ended up going with metadata in the comment. E.g.

Hey <mention userid="785" tagname="JohnnyBravo"/>! 

I included the user's name (tagname) as well so that client application can extract the name directly from the comment text instead of adding another step to look up who user 785 is.

Answer 1

If @name is not unique,you have to somehow associate the non-unique name, via the session, with the unique owner of the natural name, and do this ideally before storing it in the database. Storing a non-unique name in the database, if it cannot be resolved to its unique owner, is not of much value.

Since you mention "sql" I assume you're using a relational database. If that is the case, once you have resolved @name to its unique owner, I would create a one-to-many relationship between posting or comment and userids; that would allow a comment or post to reference more than one user.

TABLE: COMMENT_MENTIONEDUSERS
commentid
userid

Answer 2

The big problem here is if the username is not a stable reference, you need to abstract it to an id reference, while still keeping the the text reconstructable, but the references queryable.

Embedded collections and dynamic typing are a great option if you're using a NoSQL database. It would be fairly straightforward.

{
  _id: ...,
  text: [
    "Wow ",
    51, 
    ", your selfie looks really great, even better than ",
    72,
    "'s does."
  ],
  ...

}

That way you could query references, while still easily reconstructing the content. BUT since you're using SQLAlchemy, that's a no go. Your methodology seems fine, but because your doing magic in the string you'll need to escape your delimiters, (as well as escape the escape character) if they exist in the text. Personally, I would use @ as the delimiter since it's already a special character. You'd also need to identify the end of the id, in case the user sticks a bunch of numbers after the @mention, so

Wow @51@, your selfie looks really great, even better than @72@'s does. email me! john\@foo.com. Division time!!! with backslashes! 12\\4 = 3

IF querying posts for references is also important to you. You'll also need to maintain a separate POST__USER junction table that stores a row for the post and for each user id, so that when you load an object into memory, you can construct a collection. You could decide to add the junction table later, but it would be a fairly expensive migration.