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),
) 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.
> 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