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
