List:General Discussion« Previous MessageNext Message »
From:Hank Date:October 19 2012 7:26pm
Subject:Re: Schemas for storing and reporting on hashtags
View as plain text  
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.

-Hank


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
> situation.
>
>> -----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
>> features.
>>
>> I'm also looking for a solid basic implementation, not something overly
>> complex.
>>
>> Thanks,
>>
>> -Hank
>>
>> (query, mysql)
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql
>
Thread
Schemas for storing and reporting on hashtagsHank19 Oct
  • RE: Schemas for storing and reporting on hashtagsRick James19 Oct
    • Re: Schemas for storing and reporting on hashtagsHank19 Oct
      • Re: Schemas for storing and reporting on hashtagsDehua Yang20 Oct
        • Re: Schemas for storing and reporting on hashtagsHank25 Oct