0

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?

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)

  • I've seen several related search DB questions and many of the answers suggested using a No-SQL solution, but why ? In terms of scalability , I thought in using a SQL cloud solution, but why do you say No-SQL is better for this ? – gabdev Jun 2 '18 at 20:08
  • 1
    @gabdev I wouldn't go as far as to make a definite statement on the matter, but I would definitely put some work in to gage the scale of the eventual product. I have a feeling that a good search will be computationally expensive and not a simple sql query – Ewan Jun 2 '18 at 20:19

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.