[Need Advice] Question filter

[Ganners]

I am working on a site for a client where there are a list of products. He wants the user to be able to have drop down boxes to select parent questions, then another box will appear with child questions to filter through the database of products.

 

What is the best way of doing this? I was thinking about:

 

-Having a table for products

-Having a table for questions

-Having a field in the products table called "QuestionIDs" and it containing something like "1, 5, 9, 21, 15" (so id's seperated by a comma to the corresponding questions which describe this).

 

Is this a good way? Is there a better way?

 

Thanks for your help

[thehippy]

That's a bad way to do it.  RMDBs are built on the design philosophy of creating relationships with data, a single cell for a single piece of data.  Read up on 'Fundamentals of Relational Database Design', it'll help you as a developer better use databases.

 

If Questions are unique to the product then you'll want to create a has many relationship from the Product to Questions.  Product hasMany Questions.

 

productID, name, description, ...

 

questionID, productID, question

 

-- Select Questions for a specific productID
SELECT q.questionID, q.question 
FROM question AS q 
WHERE productID = #

 

If Questions are not unique and many products can have many of the same questions, then you'll want to create a many to many relationship, this requires an extra table.  This is the common way to reduce a database from having redundant data, in this case it gets rid of having duplicate questions in the table.

 

product table - productID, name, description, ...

 

question table - questionID, question, ...

 

producthasquestions table - productHasQuestionsID, productID, questionID

 

-- Select Questions for a specific productID
SELECT q.questionID, q.question 
FROM question AS q, productHasQuestions AS h
WHERE (h.productID = #) AND (q.questionID = h.questionID)

[Ganners]

Ah okay I understand this, I'll be using a many to many.

 

Thanks for your help thehippy!