CODEWITHSUNDEEP
mysqldatabasetagsdata-modeling
user862010
user862010

Reputation:

Best model for a system of tags

what is the best model for a system of tags? for example, a topic can have N tags, tags and N may be related to N threads, if I create a table, tags and posts a table and this table tags, I had the ID of the posts, and the tag, would be duplicating many records as another topic to add this tag, which is the best way to avoid this?

enter image description here

@Quentin, the problem continues, it will repeat the records in the table post_tags

CREATE TABLE IF NOT EXISTS `posts` (
  `pid` bigint(22) NOT NULL AUTO_INCREMENT,
  `author` varchar(25) NOT NULL,
  `content` mediumtext NOT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `posts` (`pid`, `author`, `content`) VALUES
(1, 'Andrey Knupp Vital', 'Hello World !');

CREATE TABLE IF NOT EXISTS `tagged` (
  `pid` bigint(22) NOT NULL,
  `tid` bigint(22) NOT NULL,
  PRIMARY KEY (`pid`,`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tagged` (`pid`, `tid`) VALUES
(1, 1),
(1, 2);

CREATE TABLE IF NOT EXISTS `tags` (
  `tid` bigint(22) NOT NULL AUTO_INCREMENT,
  `tag` varchar(15) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `tags` (`tid`, `tag`) VALUES
(1, 'main'),
(2, 'hello');

Upvotes: 2

Views: 477

Answers (1)

Quentin
Quentin

Reputation: 944530

Use a junction table

It will have at least two columns. "Posts" and "Tags" with the primary key being defined across the pair of them (if the database allows it, otherwise a third column for the primary key).

Upvotes: 7

Related Questions