php faceted search

Question

hi,

i have table question(mysql with php) which contains question,category(multiple) and subcat(multiple) its belong. for simplification while saving each question i saved the category in comma separated as below

qid    question      catid    subcat
2     question1        2,3,4   5,7

like this i have 1000 of question right now in DB

while coming with faceted search i want to filter out for each category and its corresponding subcat like tree format below

 cat1(5)
   subcat1(3)
   .........
 cat2(24)
 ..........

For this i just implemented in_array while searching for particular cat for each question in for each loop for that question table.The page getting slow now . along with text search also.

is it really good idea or suggest me some right way .

whether i wanna seperate table for category ?

Thanxs, Gobi

Answer 1

Yes the right way to do this is to have separate tables for both category and sub-category as:

Table Category
--------------
qid   cat-id


Table Sub_category
------------------
qid   sub_cat_id

This way your question table reduces to:

Table Question
---------------
id    Question