Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How many columns is too many?
- Thread starter carlg
- Start date
Generally speaking a table is a list of things. The things are described by the columns. If your project involves things which you are describing with 300 properties then 300 is the right number of columns.
However, it seems unlikely to me that is what you plan to do with the 300 columns. It seems more likely that there are repeating groups of columns which are the same properties of the things, possibly occurring monthly, or at various sites, or in various classrooms. That is they actually represent other things which are related to the basic thing in the table. If so, then they need to be moved into their own tables.
Why not tell us what you plan to do with those 300 columns.
However, it seems unlikely to me that is what you plan to do with the 300 columns. It seems more likely that there are repeating groups of columns which are the same properties of the things, possibly occurring monthly, or at various sites, or in various classrooms. That is they actually represent other things which are related to the basic thing in the table. If so, then they need to be moved into their own tables.
Why not tell us what you plan to do with those 300 columns.
- Thread starter
- #3
I really only need about 150 columns. I asked about 300 in case of future expansion.
But anyway, my table would have a key unique identifier field, about 10 text fields and 125+ true/false type of fields (boolean).
I thought about turning the 125 fields into 1 field of varchar(125).
Then each character of the new field would be a true/false value, but then my php code would have to parse this which is doable but I'm trying to keep it simple
THanks for the info
But anyway, my table would have a key unique identifier field, about 10 text fields and 125+ true/false type of fields (boolean).
I thought about turning the 125 fields into 1 field of varchar(125).
Then each character of the new field would be a true/false value, but then my php code would have to parse this which is doable but I'm trying to keep it simple
THanks for the info
Questionnaires are an application domain which have data that might be stored in a structure like the one you described with a few text fields for open-ended responses and many integer and boolean fields for multiple choice, ratings, and true/false responses.
One way to represent the returned questionnaires is with three tables, SurveysReturned, SurveyAnswers, and SurveyComments.
The SurveysReturned table has one row for each questionnaire that is returned, the thing represented in this table is a questionnaire completed by someone. The columns are survey_id which is the primary key, plus 10 other columns which are facts I need about the questionnaire source, date received, etc.
The thing represented in the SurveyAnswers table is a response to a question on one of the surveys. The columns are survey_id which is a foreign key referring to the questonnaire; answer_id which is a primary key that is unique over all of the answers to all questions on all of the questionnaires; question_id which tells which question this is the answer to; and answer which is a code indicting what the answer to the question was.
This table is particularly relevant to your application, I think. Where you are thinking about having 125 true/false columns, you might instead have 125 rows in such a table. Each row with a column for the true/false value, a second column to identify what it is that is true or false, and a third column referring to the key unique identifier field in your basic table.
There are two advantages to this approach. One is that it can be extended to 126 items, or even 526 items, often with little or no changes to any queries, database design, or application programming. The other advantage is that reporting will be straightforward using SQL GROUP BY statements and simple aggregate functions SUM(), AVG(), and COUNT().
The things in the third table are verbose, non-coded answers to questions. Rows are added only for actual comments; since most open-ended items are not answered there is no storage wasted for those questions. There is a survey_id column, an answer_id column, and a text column.
One way to represent the returned questionnaires is with three tables, SurveysReturned, SurveyAnswers, and SurveyComments.
The SurveysReturned table has one row for each questionnaire that is returned, the thing represented in this table is a questionnaire completed by someone. The columns are survey_id which is the primary key, plus 10 other columns which are facts I need about the questionnaire source, date received, etc.
The thing represented in the SurveyAnswers table is a response to a question on one of the surveys. The columns are survey_id which is a foreign key referring to the questonnaire; answer_id which is a primary key that is unique over all of the answers to all questions on all of the questionnaires; question_id which tells which question this is the answer to; and answer which is a code indicting what the answer to the question was.
This table is particularly relevant to your application, I think. Where you are thinking about having 125 true/false columns, you might instead have 125 rows in such a table. Each row with a column for the true/false value, a second column to identify what it is that is true or false, and a third column referring to the key unique identifier field in your basic table.
There are two advantages to this approach. One is that it can be extended to 126 items, or even 526 items, often with little or no changes to any queries, database design, or application programming. The other advantage is that reporting will be straightforward using SQL GROUP BY statements and simple aggregate functions SUM(), AVG(), and COUNT().
The things in the third table are verbose, non-coded answers to questions. Rows are added only for actual comments; since most open-ended items are not answered there is no storage wasted for those questions. There is a survey_id column, an answer_id column, and a text column.
- Status