Database Tagging with alternative Results

Question

Hi,

I'm working on an PHP/Jquery autosuggest tool that'll query a large table of tags but I need some suggestions for the db schema that'll return related tags as well as match-based results.

For example:

I type 'Web':

Results

'Web Developer'

'Web Designer'

'Web Analyst'

and also related tags...

'PHP Developer'

'Flash Programmer'

So obviously there needs to be a column for each tag with some sort of relational or parentage value... Whats the best way to go about this?

Thanks!

--- UPDATE -----------------

certainly - and thanks again for all your help!

here are the tables..

Tags

Id TagName

1 Web Developer

2 Web Designer

3 Web Analyst

4 PHP Developer

5 Flash Programmer

RelatedTags

TagId RelatedTagId Strength

1 4 0

1 5 7

Since the Strength value of 'Flash Programmer' is 7 i'd like it to appear before PHP programmer..(hopefully the format is clear enough..)

thanks again!

Answer 1

Table Tags

Create Table Tags (Id int, TagName varchar(50))

Table RelatedTags (both fields FK to Tags(Id) )

Create Table RelatedTags(TagId int, RelatedTagId int)

Query for selecting tag matched input and related tags

SELECT Id, TagName, Strength
FROM Tags
WHERE TagName LIKE 'Web%'
UNION
SELECT Id, TagName, Strength
FROM Tags 
WHERE tags.Id IN (SELECT RelatedTagId
       FROM Tags t
       JOIN RelatedTags r
       ON (t.Id = r.TagId)
       WHERE t.tagName LIKE 'Web%')
ORDER By 3 DESC