Database Design – Best Practices for Tagging System

database-designindexingtagging

I'm creating a system where items will have one or more tags.

Now there will be an item table that will store each item, and a tags table, that will store all tags used in the system.

My question is: What is the best design I can have that will allow me to link items to their respective tags, and record how many times an item received a particular tag from a user?

For example, I'm wondering if I should create a third table that would associate a particular item with a particular tag and how many times that tag was selected. The fields would be like this: item_id, tag_id, tag_hits – where tag_hits is the number of times the tag was used. This solutions would associate all items with their tags in a single table.

Or the other solution I'm thinking of is to create a table for each item and record each tag and the amount of the time the tag was used. So, for itemX I would create a table itemX and the fields would be: tag_id, tag_hits. For another itemY, I would create a table called itemY with the same fields. So for every item, there would be a table associating it with its tags. Now there could be hundreds of thousands of items in this system.

I suppose the developers of this website had to make similar consideration because each question is tagged. And you can browse questions by tag. My system will have similar functionality.

Can someone recommend a design solution, taking into consideration all that I have said, and performance consideration. Please indicate where you think indexing might help also. I use SQL databases in my development, but if you think a no-SQL solution would do it, please suggest a design along these lines. please be very specific.

Thanks,
Ron

Best Answer

Go with your first approach, it is pretty much the standard way of modeling this type of relationship. (updated to record user who added the tag)

Items
ItemID (PK)

ItemTags
ItemID (FK)
TagID (FK)

Tags
TagID (PK)

UserTags
ItemID (FK)
UserID (FK)

You could also do this by adding a UserID column to the ItemTags table and allowing duplicate rows for each tag, but the above approach his likely to be more performant.

The second approach seems like a bad idea since it wouldn't give you the flexibility to add more tags in the future and likely would make queries more difficult to write.

As for indexing: Index each of the foreign keys (the primary keys should automatically get indexed).

Use an RDBMS, a NO-SQL solution probably wouldn't give you any benefit for this type of data structure.

Whatever you do, avoid creating multi-value fields with some kind of comma or space delimited values. It is a performance killer and makes queries really hard to write.

Related Solutions

SQL – Database for Storing Books by Grouping, Pages, and Table of Contents

A number of recommendations and notes.:
1) You have to list an order of the pages somewhere (I would not rely on anything but a specific 'order' column for this) - This can either be accomplished with a 'page_number' type column (like position_in_TOC would seem to imply), or a linked-list type equivalent ('next page has this id').
2) There is a huge possibility of needing multiple groups per book; I would recommend a book_group correlation table, allowing you to have books that deal with things like ORM framworks or program database access (with groups like 'Java', 'SQL', 'C#', 'Hibernate', etc.).
3) Instead of having a 'nested factor', consider using a recursive parent-child relationship (which is precisely what the relationship is). This will also make changing which section is nested where much easier.
4) No matter what you do, you're going to have some sort of 'complex' programming. You're just going to have to deal with it.

I think a slightly more flexible design is closer to this (note: this has not been completely normalized, but should be an okay starting place):

Table Design

This allows for several things:
1) Books can be part of multiple groups, or none.
2) Sections are specifically listed as children of other sections (null if they are the top). This allows sections to be moved to be inside a different parent by simply reassigning parent_section.
3) Pages and sections both have an ordinal index. This allows them to be reordered inside their containing section, without regard for their parent's index; Page 1 is page 1 of that section, not of the entire structure, eliminating the need to re-order the entire tree if only one section changes.

There are a number of things that could be done to muck with this design, maybe adding section-heading pages or something.

Database – How to design a database wherein multiple tags(string) are to be associated with an id

I'd suggest having one table for tags, one table for "audios", and a third table to keep track of which tag is associated with which audio. Something like:

tags_audios

audio_id   | tag_id
----------------------------
1          | 2
1          | 3
2          | 1
2          | 3
3          | 6

This is usually how I see m:n relationships stored, and it is pretty close to Solution #3 which you proposed.

Solution #1 will give problems when you decide you do want more than 15 tags. You will also have to search all of the tag columns when you want to find just one value.

I can't think of a good situation to use Solution #2, except maybe in a reporting system where aggregating all of the tags into a string will make the final report easier to generate.

Best Answer

Related Solutions

SQL – Database for Storing Books by Grouping, Pages, and Table of Contents

Database – How to design a database wherein multiple tags(string) are to be associated with an id

Related Topic