Dehua, Thanks. You are correct. Perhaps I was just over-thinking it.
-Hank On Fri, Oct 19, 2012 at 9:48 PM, Dehua Yang <meflyingf...@gmail.com> 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 <hes...@gmail.com> 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 <rja...@yahoo-inc.com> 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:hes...@gmail.com] >> >> 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 : meflyingf...@gmail.com > Skype name : meflyingfish > Twitter: http://twitter.com/whitepoplar > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
