List:General Discussion« Previous MessageNext Message »
From:Dehua Yang Date:October 20 2012 1:48am
Subject:Re: Schemas for storing and reporting on hashtags
View as plain text  
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