Jump to content

Archived

This topic is now archived and is closed to further replies.

Ganners

[Need Advice] Question filter

Recommended Posts

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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

 

Thanks for your help thehippy!

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.