MySQL Forums
Forum List  »  Performance

Re: tables with many, many columns
Posted by: Harrison Fisk
Date: October 28, 2004 12:22PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
5549
October 27, 2004 01:59PM
2931
October 28, 2004 08:44AM
2524
October 28, 2004 09:45AM
Re: tables with many, many columns
2827
October 28, 2004 12:22PM
2479
October 30, 2004 04:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.