Table exceeding 255 fields? 3

Pyrrhus

I suspect you need to "normalize" your database design rather than design a table with 400 fields.

Please review the document on relational databases kindly provided by a prolific contributor to Tek-Tips, JermeyNYC

I do not have specifics on your database so I can not make specific comments ;-)

Here is a recent link on a very similar issue with rebuttle.

Richard

Richard,

Please quote the original author of this document. It is hosted on JeremyNYC's website but is by Litwin et al.

Craig

Craig, you are absolutely correct.
Fundamentals of Relational Database Design was written by Paul Litwin

Another link can be found at...

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Thanks Richard for the links (and Skip for the direct link). I’ve read them but maybe you can give me some advice on my specific situation.

I need a db to store and analyze results of a survey. Each “result” consists of 320 items each of 2 digits max. (I said 400 before, but I’ll split the extra 60 or so into separate tables). Values can be only 1-10, x (for not applicable), or B for blank (question missed or not scored). The 320 scores are comprised of 40 topics of 8 questions each. The primary key would presumably be the email address of the respondent. (I have a second, separate survey app which has 40 topics from 6 to 14 question each.)

I’m not 100% sure of what reporting criteria will emerge. Obvious are average scores and statistical analysis. Probably is a need to search for best fit profiles etc.

I refer to each score by (for example) s1205, where s stands for score, 12 is topic 12, 05 is question 5 in that topic.

I suppose the simplest setup would be a separate table for each topic. Eg. for topic 12:

Email-address s1201 6
Email-address s1202 10
Email-address s1203 x
Email-address s1204 9
Email-address s1205 2
Etc.

(Alternately, table "s12" field "05", etc. ?)

My immediate dilemma in choosing a design is how to get the data in.

I currently receive it by email in the form”

s0101 2
s0102 x
s0103 9
.
.
s4008 3

It would be easy to import this data as a single string if the table was a single table of 320 fields. My question is, if I break it up into 40 topics tables, how can I import the data in a semi-automatic (at least) way. Do I use VBA?

Appreciate any help.

Thanks.

Pyrrhus

A database design for a survey can be a little complex - lots of things to consider...

Topic
TopicID - primary key
SortOrder - integer, used for sorting questions
TopicDesc - text, descrption
+ other info on topic

ResponderTbl
ResponderID - primary key
ResponderEmail - text field
+ other info on the responder

QuestionTbl
QuestionID - primary key
TopicID - foreign key to Topic table
QuestionTxt - text field
+ other info regarding the question being asked
(For example, purpose of the question, date question was last edited...)

Next is to generate the questions. There is a one-to-many relationship between topic and question. One topic can have many questions. Normally this would mean that you would add the TopicID to the QuestionTbl. If you do this, you will also need to add a SortOrder field to control the order of questions.

However, some questions can technically be asked more than once, and from experience, I am going to suggest using a many-to-many relationship and setup a joiner or profile table. This gives you flexibility later on.

TopicQuestionTbl
TopicID - foreign key to Topic table
QuestionID - foreign key to Question table
SortOrder - interger
+ anything else to pertaining to topic and question.
Primary key is TopicID + QuestionID

To generate the survey, you go through each topic int the topic table in sort order and using the TopicQuestion table, find out each question for the topic in sort order.

Now to capture the answers...

Since one responder can respond to many questions and one question can have many responses, there is a many-to-many relationship between the responder and question. A M:M table uses a joiner table, and since this same table will capture the responses, a good name to use is ResponseTbl.

ResponseTbl
ResponderID - foreign key to Responder table
QuestionID - foreign key to the question table
Response - hmmm, what do we do here???
+ any additional info on the response (date, comment or memo field)

The primary key is ResponderID + QuestionID

There are still a couple of tough issues here - what type of field to use to accept the response and how to generate the responses to the questions.

First, how do you capture the information. If this is one survey - a one shot deal, and the answer is always number, a numeric field will suffice (I am going to ignore the "b" for blank at present). A text field could also be used, and then use logic to pull off numbers, Yes/No, a date, or short answer. A "variant" field could be used for the same purpose.

Something to conders is to have multiple fields to accept the response, and then just use the appropriate field for the appropriate exected response. I feel this is a better solution if you plan to use other surveys in the future, OR expect different responses - numeric, date, Yes/No, short answer. This requires two things to work.

First the, question table needs to flag the type of field to use for the response.

QuestionTbl
QuestionID - primary key
TopicID - foreign key to Topic table
QuestionTxt - text field
ResponseType - text used to NU = numeric, TF = true / false, TX = text, DT = date, MM = memo

ResponseTbl
ResponderID - foreign key to Responder table
QuestionID - foreign key to the question table
ResponseNU - integer
ResponseTF - true / false
ResponseTX - text
ResponseDT - date
ResponseMM - memo

Generating the responses
Since you need to capture missing answers in addition to responses, you actually want to generate a response for each question, and then capture the answers.

When I approached this same issue a couple of years ago, I used coding to generate the survey responses instead of using a complex SQL sentence. Coding gave me more control, and allowed me to decide which field to display to capture the answer.

I created a form that took in the information of the responder, then with a click of a button, generate the responses minus the answers in a suborm, and then filled in the answers.

One more thing...
The survey application I created needed to generate different surveys. So I added one more layer to the design...

SurveyTbl
SurveyID - primary key
SurveyTitle - text field

and modified the Topic table

Topic
TopicID - primary key
SurveyID - foreign key to survey table
SortOrder - integer, used for sorting questions
TopicDesc - text, descrption


To analyse the results, you already have the question + response for each responder. And you need to can retrieve the information by topic. The tougher part is to determine how many of each response was received.

The cool thing is that you can actually create a query to display the answers in a worksheet and copy the worksheet to a spreadsheet application which is a better tool for this type of anlysis.

A lot to digest here -- I hope it helps.

Richard

Wow, that was a lot of work Richard!

I guess the analogy is that with a normalized order entry database, you don't have a seperate field for each item, the 'order detail' contains the primary key order_id and order_sequence, with an item_number that can not be null. Then summary queries are used to arrive at the results.

You would have a survey detail table with a survey_id and question_number that is the primary key and user_answer that can not be null (unless they are allowed to abstain)

Rusty

The early bird gets the worm, but the second mouse gets the cheese.

Richard. That's an amazing explanation. Some great ideas. It will take me quite a while to work through them, but I thought I'd get back to you now to say thanks.

As of now the surveys have already been developed (in html & javascript) and the urgent requirement is to capture & process the data.

I think at this stage I'll take a tip from your last suggestion and use Excel to process the results, as I started to do. However, Excel also has a 255 limit on columns, which still leaves me with something of a problem. Could always break it into 2 spreadsheets, I guess, and link them into summary sheets. Just means 2 data imports instead of 1, but I guess that could be set up as a macro (if that terminology is still appropriate).

I'll go to the Excel forum to see if anyone has any suggestions for getting around the Excel 255 limit.

Thanks again. Much appreciated.

Thanks to you too, Rusty.

Thanks Rusty

Design has always come easy to me, and the original post did not take me too long - I type fairly fast, and I successfully used this design a couple of years ago.

The difference with a survey design vs an oder entry system is that the survey has the question component which is similar to the order entry system. But then you have the answer component which another layer from the other side. Of course, the M:M stuff adds complexity.

[tt]
Survey ----> Topic ----> TopicQuestion ----> Question
1:M 1:M M:1

Responder ----> Response ----> Question
1:M M:1
[/tt]

Handling the questions is a tough issue since with a generic design, you don't know what type of data type to expect. Restaurant Satisfaction Survey - numbers only, perhaps -- food great - (5) great, waiter/waiteress (5), cleanliness... But then you get the question text ones - Would you come back ? What did you order ?

Then take the same design and apply it to Check List (variation of a survey) - Desktop new build preflight - Got passwords y/n, Backup drive y/n, old asset tag - text, etc.

You can probably normalize the answer type I suppose, but it would add much more complexity and most likely hinder the analysis.

Using a simipler solution and use a text or variant field to accept the data would work when entering the data, but again, using various data types in one field may hinder the analysis.

There are simiplier solutions. My sister had to come up with a simiplier design for something similar a while back. I had the last laugh because she had to go back and modify the the design and the code again and again and again to accommodate the whims of management.

You bring out a good point about manditory answer or not. My actual design includes a bit more detail...

+ other info regarding the question being asked

Click to expand...

I have a mandatory field flag Y/N, and also the owner of each question, topic and survey along with review date and date question changed or created, etc (for ISO certification) I skipped this deail because, from my perspective is not important to the design issue, and can be added later with minimal impact to the design.

Pyrrhus will have a bit to digest, and I hope he/she agrees that it is a better solution than 300 fields. He / she is the judge because they will have to create and support the database.

Time to take the dog for a walk...
Richard

Would this be a situation to create a class module? Or is there still a 255 property limit to a class?

I had a similar instance where i wanted 300 fields, it was determining which catalogs an item appeared in. I created a hexidecimal with bits representing the 1000+ catalogs, and functions to translate back and forth when necessary.

I never want to go that route again!

Thanks for responding to my posts Richard ... i've only been immerging myself for the past few months and would like to know when there is a better solution than i suggest. But i also feel for the people posting with their problems, because just a few months ago i couldn't even get to the VB screen on an Excel sheet!

The early bird gets the worm, but the second mouse gets the cheese.
-------------------------------------
92.5% of statistics mean nothing.

Interesting,

I develop new, and maintaining existing, web based surveys for various university research projects. I develop these using the html-javascript-php-mySQL combination.

The interesting part is that the relational model that I use for the databases I develop is almost exactly the same as Richard has illustrated above!

The only difference being is that I was able to make assumptions about the information captured, namely, that the responses to questions were either numeric or short text answers (e.g. <25 characters in length). Thus I stored all the responses as string. My php code would simple cast from string to numeric if it ever needed to look up participant responses and calculate something that takes numeric arguments.

We do have a "comments" box on the last page of the surveys, which I save this into its own relation, e.g. R = {participant_id, comment}

Furthermore, with this design i can have one SIMPLE php function that handles all of the saving of the data to the database, and one SIMPLE function the handles retreiving participant responses to one or more questions which returns an associative array.

It is also very simple to track/monitor participants, e.g. number of completes, incomplete and even how long it took them to do the survey as this can be an important determinant of response rate (along with other factors). Also, it is simple to add to the design (as Richard has pointed out).

I’m not 100% sure of what reporting criteria will emerge. Obvious are average scores and statistical analysis. Probably is a need to search for best fit profiles etc.

Click to expand...

We do all our reporting and analysis in a statistical package (e.g. SAS, SPSS, Stata). Importing the data is simple and you can save your script for re-use. I hope you will check your data conforms roughly to a normal distribution before computing averages

The databases that were created before I arrived were written as a flat file (~ 200 columns), and all i can say is - that it wasn't very pleasent working with it!

Cheers,
Dan

Rusty

I believe the limit is in the design level - so many fields, so many tables allowed.

There are perhaps work-arounds. For example, create 2 or 3 tables similar in design, and create one-to-one relations betweent them, but I go back to the design being the principle problem.

With your current design, enter some data, and then try and create your reports and analysis -- see if the work is easy or not. If you get it working, I suspect you will have to do a lot of "hard coding".

From my perspective, it seems that yo are using the Access cool form feature to present your questions with a list of check boxes.

Dan Curious that your design and mine presented are similar - interesting. I agree that the coding and analysis is much simplier. BTW I have not used SAS in years - glad to see it is still around.

Richard

Richard, this was Pyrrhus' thread, not mine. I'm just in it for the 3rd party learning.

Thanks!
Rusty

The early bird gets the worm, but the second mouse gets the cheese.
-------------------------------------
92.5% of statistics mean nothing.

Whoops - I guess Pyrrhus has not responded since May 20/04 so he / she has their answer...