List:General Discussion« Previous MessageNext Message »
From:Hank Date:October 25 2012 5:22pm
Subject:Re: Schemas for storing and reporting on hashtags
View as plain text  
Dehua,

Thanks. You are correct.  Perhaps I was just over-thinking it.

-Hank


On Fri, Oct 19, 2012 at 9:48 PM, Dehua Yang <meflyingfish@stripped> wrote:
> Hi Hank
>
> I just can think like this table
>
> CREATE TABLE xyz (
> hashtag VARCHAR(...) NOT NULL,
> comment_id ... NOT NULL,
> user_id bigint unsigned not null,
> PRIMARY KEY (hashtag, comment_id),
> INDEX(comment_id, hashtag),
> index idx_user_id(user_id)
> ) ENGINE = InnoDB;
>
>
>  one user want to check all his comments
> select * from xyz where user_id=x;
>
> Actually, I think your case is very classic.
>
> Hope that you can tell me how would you to resolve this problem.
>
>
>
> On Sat, Oct 20, 2012 at 3:26 AM, Hank <heskin@stripped> wrote:
>>
>> 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
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql
>>
>
>
>
> --
> Gtalk : meflyingfish@stripped
> Skype name : meflyingfish
> Twitter: http://twitter.com/whitepoplar
>
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