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
