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:\[code\]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\[/code\]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 thisIn which they have
- a 'tags' table which contains twocolumns a primary id and a uniquelyindexed tag_name.
- a 'type' table for which they haveanother primary id and a uniquelyindexed 'type' (category) (I thoughtI could use it foruser/video/article/gallery)
- a 'search' table that contains theurl of the article with a foreign idfrom 'tags' and 'type'. (I thoughtinstead of a full url I could juststore the related foreign id so thatI can generate the url e.garticle.php?id=....)