TrailHunter
Programmer
Hello, I just stumbled onto this site and it has a lot of great information!
Onto my problem. I'm trying to create a product review system where I can have any product of any type. By type, I will be organizing them into categories where each type of product will have a certain set of review questions that they must answer. For example, there might be a tires category and I want to know things like the tire size, wear, road noise, wet traction, etc and then stereos a totally different set of criteria.
At this point I have a table for the categories and another for the questions because the number of questions will vary between the categories and the type of data/answer each question is expecting will be different. It might be a yes or no question or it might be an int from a 1-10 rating scale.
The problem I have is how to store each user's reviews. I dont want to create a table with say ten columns to use for answers when some categories may only have five questions. I'm leaning towards putting each answer on it's own row which is very cumbersome because I will have to have n number of INSERT queries for n questions in a review. Any advice there?
My other dilema is each question in a review is expecting different data types. Should I just create a long varchar or even a text column and programitcally determine what should be there? I will use these reviews to generate product rankings so I don't want to massage the data to get these reports when a simply query could be done.
This is all PHP/MySQL so if there are any features that I'm not aware of that can help me solve this problem of different data types for every answer in a review. Just a thought, should I create a column for each datatype and just use the column that applies to a specific answer.
Thanks for looking!
Onto my problem. I'm trying to create a product review system where I can have any product of any type. By type, I will be organizing them into categories where each type of product will have a certain set of review questions that they must answer. For example, there might be a tires category and I want to know things like the tire size, wear, road noise, wet traction, etc and then stereos a totally different set of criteria.
At this point I have a table for the categories and another for the questions because the number of questions will vary between the categories and the type of data/answer each question is expecting will be different. It might be a yes or no question or it might be an int from a 1-10 rating scale.
The problem I have is how to store each user's reviews. I dont want to create a table with say ten columns to use for answers when some categories may only have five questions. I'm leaning towards putting each answer on it's own row which is very cumbersome because I will have to have n number of INSERT queries for n questions in a review. Any advice there?
My other dilema is each question in a review is expecting different data types. Should I just create a long varchar or even a text column and programitcally determine what should be there? I will use these reviews to generate product rankings so I don't want to massage the data to get these reports when a simply query could be done.
This is all PHP/MySQL so if there are any features that I'm not aware of that can help me solve this problem of different data types for every answer in a review. Just a thought, should I create a column for each datatype and just use the column that applies to a specific answer.
Thanks for looking!