Which Search / Tag system is better?

Question

I am working on a website that has users and user-generated articles, galleries and video's. I am trying to make a tagging system and a search for them all.

At first I was thinking in tbl_articles, tbl_galleries and tbl_videos I would have a title, description and a tags field. Then run a query like the following for each:

select * from tbl_articles where match(title, description, tags)
  against ('$search' in boolean mode) ORDER BY match(title, description, tags)
  against ('$search' in boolean mode) DESC, views desc LIMIT 0, 3

The same query for tbl_galleries and tbl_videos. For the users just compare the username. Then display three of each on the results page with a 'more' button (facebook style).

When viewing an article, gallery or video there will also have links to related content so I was thinking of using the same query only with the LIMIT set to '1,3' - to avoid showing itself.

Q1 - How is this system?

I was happy with the system, until I found this

In which they have

• a 'tags' table which contains two columns a primary id and a uniquely indexed tag_name.
• a 'type' table for which they have another primary id and a uniquely indexed 'type' (category) (I thought I could use it for user/video/article/gallery)
• a 'search' table that contains the url of the article with a foreign id from 'tags' and 'type'. (I thought instead of a full url I could just store the related foreign id so that I can generate the url e.g article.php?id=....)

Q2 - This system seems far more efficient... although how would I search the title or description?

Q3 - The other bad thing is for every page view I would have to join the tags.. so it might not be that much more efficient.

Q4 - My system only searches boolean too, would I be better with a 'like' query?

Q5 - I limit my users to 4 tags, but I encourage single-words (stackoverflow style)... I realise though that in my system a search for 'train station' will not match a tag like 'train-station' how do I get around this?

So many questions... Sorry it is so long. Thank you.

Answer 1

Q1 - you're better off with three separate tables for articles, tags, and a link table relating articles and tags. You could also do it with two tables for articles and an articles_tags table. The articles_tags table would contain an articleID field and the tag itself as a compound key. Either two or three tables makes it easy to find which articles have a given tag and which tags are assigned to a given article.

Q2 - title and description searches could be done using "like" with percents or with regex or full text search.

Q3 - don't worry about joining the tags tables with the others. To paraphrase Knuth, build it first, then find the bottlenecks. MySQL is very good at what it does. Joining those tables together over and over and over again won't hurt.

Q4 - It depends what you want to get out of the results. usually you want the actual data and then you can just test for the number of rows returned to tell you whether it's true or false.

Q5 - again, you'd have to use "like" syntax and maybe some creative regex on the PHP side before the query is handed off to the database.

good luck!