It's actually much more complicated than a simple many-to-many
relationship. For instance, users may enter many hashtags in many
comments, and a user might want to see all the hashtags they've used,
and then find all the comments with those hashtags.
I'm not trying to re-create the specification or build it here in
discussion. I'm looking to see if anyone has it done already, and I
can review their flushed-out design. I've been a database architect
for 20+ years, so I know what I'm doing. I'm not asking for people to
show me what to do. Like I said, I could sit down and design it
myself pretty quickly, but I would like to see what other people have
*actually done* to solve the problem before.
On Fri, Oct 19, 2012 at 2:42 PM, Rick James <rjames@stripped> wrote:
> Many-to-many? That is, can a comment have many different hashtags? And a hashtag
> can be associated with many comments?
> Best practice for many-to-many:
> CREATE TABLE xyz (
> hashtag VARCHAR(...) NOT NULL,
> comment_id ... NOT NULL,
> PRIMARY KEY (hashtag, comment_id),
> INDEX(comment_id, hashtag)
> ) ENGINE = InnoDB;
> One might want to "normalize" the hashtags, but it does not seem warranted in this
>> -----Original Message-----
>> From: Hank [mailto:heskin@stripped]
>> Sent: Friday, October 19, 2012 8:58 AM
>> To: MySql
>> Subject: Schemas for storing and reporting on hashtags
>> Are there any established "best practices" or schemas for incorporating
>> twitter-like hashtags into a database ?
>> Let's say I have a blog with a commenting system, and I want to allow
>> people to add hashtags to the comments.
>> I could certainly create one on my own (it's not that difficult), but
>> I'd like to see what other people have done in terms of storage and
>> I'm also looking for a solid basic implementation, not something overly
>> (query, mysql)
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql