4
\$\begingroup\$

I'm implementing a "tags" features to an already working solution.

Final users need to be able to add tags to three separate sections of the solution:

  • Posts
  • Accounts
  • Groups

Each section has its corresponding table in the database, and each table contains a unique ID:

  • PostID
  • AccountID
  • GroupID

I'm thinking about implementing a tag schema similar to the one that Wordpress uses, where I'll have:

  • A tagmap table that will contain a unique ID for each "tagmap".
  • A foreign key to each section's table ID.
  • Another foreign key to the ID of the tag.

CREATE TABLE Posts(
  PostID int(2) NOT NULL AUTO_INCREMENT,
  Content varchar(255),
  PRIMARY KEY(PostID)
 );

CREATE TABLE Groups(
  GroupID int(2) NOT NULL AUTO_INCREMENT,
  GroupName varchar(255),
  PRIMARY KEY(GroupID)
 );

CREATE TABLE Links(
  LinkID int(2) NOT NULL AUTO_INCREMENT,
  href varchar(255),
  PRIMARY KEY(LinkID)
 );

CREATE TABLE tagmap(
  TagmapID int(2) NOT NULL AUTO_INCREMENT,
  PostID int(2),
  GroupID int(2),
  LinkID int(2),
  TagID int(2) NOT NULL,
  PRIMARY KEY(TagmapID)
 );

CREATE TABLE tags(
  TagID int(2) NOT NULL AUTO_INCREMENT,
  TagName varchar(255) NOT NULL,
  PRIMARY KEY(TagID)
);

I like this approach because it is decently normalized, but queries might get a bit complex. Also, it will have a lot of NULL columns every time a tag is assigned to a post but not to an account or to a group, so I'm unsure how it will behave performance-wise (the table will hold around 100,000 records almost immediately).

Is there a better alternative to this?

\$\endgroup\$
1
  • 1
    \$\begingroup\$ You have to split your tagmap table to tag_post, tag_group and tag_account. This is also evident from: "Final users need to be able to add tags to three different sections of the solution: Posts, Accounts, and Groups" \$\endgroup\$ May 31 '13 at 8:29
3
\$\begingroup\$

While this may seem "decently normalized", it's not. As you said yourself, there will be a lot of (redundant) NULL values, but also, relationships will not be enforced correctly, as this schema allows for duplicate tagmap records for any combination of Post/Group/Link and Tag. For instance, you could have two tagmap records with the same TagID and PostID.

Now, while you could add a compound key consisting of PostID, GroupID, LinkID, and TagID, then you have another problem: a single record can contain more than one (non-tag) Post/Group/Link relation. e.g. a tagmap record with TagID= 1, PostID= 1, AND GroupID= 1. This can lead to update anomalies. See Second normal form.

A normalized solution would be to have three separate tag-relation tables as abuzittin gillifirca suggested: tag_post, tag_group, and tag_link. Doing so will eliminate all the redundant NULL values and allow you to enforce relations correctly.

Having said that, I'm guessing you came up with this design, because you want to use a single query to fetch all entities assigned to one or more tags. You can still do this with multiple tag-relation tables like so:

SELECT everything.Type, everything.Id, everything.Content
FROM (
    SELECT 'post' as Type, PostID as Id, Content 
    FROM Posts 
    JOIN TagPosts ON TagPosts.PostID = Posts.PostID 
    WHERE TagPosts.TagID = ?

    UNION ALL

    SELECT 'group' as Type, GroupID as Id, GroupName as Content 
    FROM Groups 
    JOIN TagGroups ON TagGroups.GroupID = Groups.GroupID 
    WHERE TagGroups.TagID = ?

    UNION ALL

    SELECT 'link' as Type, LinkID as Id, href as Content 
    FROM Links 
    JOIN TagLinks ON TagLinks.LinkID = Links.LinkID 
    WHERE TagLinks.TagID = ?
) AS everything;

This would return records like:

"post",  1, "Some content of a post"
"post",  2, "Content for another post"
"group", 1, "Group name"
"link",  1, "http://johntron.com"
\$\endgroup\$
4
  • \$\begingroup\$ What are the performance implication of the N:N junction tables to entities? Considering that each entity you need to query will require an additional Union. \$\endgroup\$ Nov 19 '15 at 17:18
  • 1
    \$\begingroup\$ @AmadeoGallardo it would depend on the query plan, but performance should be a secondary priority - data consistency is paramount, so proper normalization is where I always start. True, many SQL engines do a poor job with the derived table, but this is something that could be improved with tuning. In really extreme circumstances, denormalization may be necessary, but I would avoid this if at all possible. Just monitor the performance of your database (a list of queries descending-sorted by execution time for instance), and make the decision when it's necessary. \$\endgroup\$
    – Johntron
    Dec 12 '15 at 19:10
  • \$\begingroup\$ As a wrapper for the conversation, ended up implementing multiple join tables, each one with a foreign key associating the tables. It is working well and through an abstraction the application model hides the data complexity. \$\endgroup\$ Dec 12 '15 at 20:47
  • 1
    \$\begingroup\$ @AmadeoGallardo that's great! \$\endgroup\$
    – Johntron
    Dec 15 '15 at 21:05
3
\$\begingroup\$

A simple solution is to include a table reference in the tagmap instead of many id columns.

CREATE TABLE tagmap(
  TagmapID int(2) NOT NULL AUTO_INCREMENT,
  RefTable varchar(255) NOT NULL,
  RefID int(2),
  TagID int(2) NOT NULL,
  PRIMARY KEY(TagmapID),
  INDEX(RefTable, RefID),
  UNIQUE(RefTable, RefID, TagID)
);

With that approach, you can easily make other tables taggable. To retrieve the tags for a post, you'd just say

SELECT TagName FROM tags
LEFT JOIN tagmap USING TagID
WHERE RefTable = 'posts' AND RefID = 42
\$\endgroup\$
1
  • \$\begingroup\$ I think that'll work nice... I'm going to benchmark this solution and the one that I already implemented and see how it goes! Thanks a bunch! \$\endgroup\$
    – ILikeTacos
    May 31 '13 at 18:09
2
\$\begingroup\$

As @abuzittingillifirca said, this isn't very normalized. Summarizing my own opinion and what I've generally gathered from the net, you'll want to normalize it completely, test it with real data, then optimize the heck out of your configuration and queries before thinking about denormalization. With proper optimization, in my experience you should be able to get to millions of rows before queries start being less than humanly instantaneous.

Also, have you studied other tagging solutions and their requirements? There's probably a lot of stuff that's already been figured out...

\$\endgroup\$
1
  • \$\begingroup\$ I've researched other tagging solutions, but the "tagmap" solution I think is the one that best suit our needs for now. But I'm more than happy to research more options! If you have a resource that you think I should check out, I'll happily do it! \$\endgroup\$
    – ILikeTacos
    May 31 '13 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.