Re: tables with many, many columns

[Harrison Fisk]

Hi,

I might recommend that you create a more normalized database schema, it might make queries much easier to construct.

Try using three tables:

companies
- information about the company

questions
- what was the question asked

answers
- contains the question id
- contains the company id
- contains the actual answer

(You might not need questions, depending on how you have defined the survey)

Why do this? Well it can make querying easier, and also more flexible. Imagine you need to have another question asked, in this model you can easily add another row to questions, whereas in yours it would require an ALTER TABLE. Imagine you want to find the average value given in company A. In your model, you would have to list out 1200 columns to add up and divide by. In my model, you could do it very simply by using a AVG() function.

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com