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.