The best way to store comments with mentions (@FirstName) in the database

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
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         ()
post_id    (id of the post that the comment is for: )
author_id  (id of the creator of the comment: )
text       (comment text: )
timestamp  (comment date: )

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 @[email protected], your selfie looks really great, even better than @[email protected]'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.

Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » The best way to store comments with mentions (@FirstName) in the database

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录