Designing an efficient search by tag solution

Question

I've a project where the users will be able to tags themselves with skills tags (maybe predefined in the system) , and then the project managers on a company would be able to search the best user with a set of skills for a given task. For example : Java would be a tag, or maybe Android , etc. I'll use a relational DB. I've so far the following options:

1. Users, UserTags, Tags tables.
2. Users and Tags tables, storing each user's tags as an user_tags text column (in Users).

N° 1 it's the most correct way to do it in terms of pure logic design , but at the time of searching it would require to query the three tables. More over, the Project Manager can also search by user's name, so maybe a query could be :

SELECT users.name, users.id FROM users, user_tags, tags WHERE (tags.name={query} AND tags.id=user_tags.tag_id AND user.id=user_tags.id) OR users.name LIKE '%{query}%' LIMIT N .

I don't know if it's an efficient query, notice I have an OR with a LIKE statement.

With option N° 2 I'll have a storage penalty (storing many times the same TAG name for each user) , but maybe I could perform a FULLTEXT indexed search on the column tags and name of User , therefore querying only one table with a FULLTEXT index. Here the Tags table will be useful as a control table, validating if the selected tags by the user are permited.

So, what do you think it's the best approach?

Answer 1

I think you are going to want to say that a project manager searching for 'c#' also gets '.net' but not 'c'

That means you'll want to group tags into categories, prevent 'C#' and 'c#' being different tags and be more clever with your search than just a word comparison.

You'll need more linked tables and want to refer to a tag by its id rather than its written english representation. So I would use the join table in this case.

(although I have a feeling that a relational db might not be the most scalable approach long term)