Building an FAQ/KB -- with related questions

need help with table definition/relations

[mgm_03]

I'm using php/mysql...

I know there are many sophisticated FAQ/KB solutions out there but I just feel the need to re-create the wheel. Some of these solutions have a feature where if a user selects a particular FAQ, a few "related" questions also appear as additional options to explore. I'd like to add that feature but not sure how to implement.

As it stands, I have 3 tables :

faqs:
fid
catID (question -- do I need to include subcatID in this table as well?)
question
answer
date

cats:
catID
cat

subcats:
subcatID
catID
subcat

At this point, do i create a "related_questions" table that has an fid column and a SET column type containing the set of fid's of related questions?

TIA!

[Nutter]

There is a way to do a full text search. I don't have the code in front of me right now, but I use it one one of my sites. Basically I do a full text search of the current article text with all the other article texts. It returns a number that is somehow associated with how similar the articles are. I have a cut off and I take the top 5 (if there are that many) above that cutoff point.

I know you have to build a full text index, but other than that, I really don't recall how I did it; just know that it can be done. I'll look tonight if nobody else pops in their two cents.

- Ryan

[mgm_03]

ok...i see what you mean...i've done the full text search before but hadn't thought about the LIMIT 5 cut-off idea.

thanks.

[Nutter]

Here's the query I use.

SELECT *, MATCH(article_body_text) AGAINST ('".$searchstring."') AS score FROM table WHERE (MATCH(article_body_text) AGAINST ('".$searchstring."') >= 10) ORDER BY score DESC LIMIT 6

I use 10 as the minimum score because it seemed a good cutoff point. Nothing scientific. But, the more articles you have, the more relavent they will be. My site's still pretty small, so a lot of the "related" articles only have a few words in common.

I pull the five most similar articles, but use limit six because the top one will the the article you're searching from. You'll have to ignore the first one in the query because of that.

- Ryan

[mgm_03]

good ideas..thanks!

[mgm_03]

....was doing some reading on mysql.com....

had you considered using IN BOOLEAN MODE

or

WITH QUERY EXPANSION

Reason I ask is becuase I believe people (like me) expect a keyword search to behave like a google search engine which is pretty intelligent tool (using stemming for e.g.).

Using mysql, if the user types in teh word 'wine' instead of 'wines' I don't believe it would be a useful tool.

'WITH QUERY EXPANSION' seems a more intelligent way to search and 'IN BOOLEAN MODE' to a lesser extent because it gives the user options to narrow (or expand) the search query

[mgm_03]

scratch that last post....unrelated to the original question. It is related to just doing FULL TEXT searches.