Question Multiple fields or multiple tables?

[byTimber]

I have to ask the user up to 50 questions each of which requires me to record 10 pieces of information

I may have 500 users. Anyone got an idea which is the best way to approach this?
As far as I've got is A) Split tables with large field numbers for all users B) Table for each user

To me, the way forward seems to be B but all those tables!!!!

Roger ....

 

[spikepl]

For masochistic fun, just try option B, for 2 users, 2 questions each. And then try to process the results - just one query, one form, one report. And imagine doing that for 500 users.

When fed up with all that, read up on database normalization - google it. One of the basic tenets is that you keep the same kind of data in one and the same container (=table), and do not create a separate container for each similar lump of data.

And one can mess it up at table level too. A field (column) in a table is also a container. If you start tagging fields with names or dates, then you are off onto a wrong track. Data is to be stored in appropriate fields, and its provenance, ownership, date, status etc is just a field too. How would you store an address list, with addresses of 5 people? Play with that.

 

[byTimber]

Thanks for reply Spike. I take your point. So I am going to need 2 tables to store the potentially 500 pieces of information from each user; say 250 fields per table (with max fields allowed 255).

I was a bit wary of having so many fields in a table and wondered whether there was a simpler way.

(I resisted your masochistic suggestion but may try it when I reckon that life is too good!!)
Best wishes,
Roger..

 

[spikepl]

say 250 fields per table

says who?

Check this http://www.access-programmers.co.uk/forums/showthread.php?t=195190 and also search for "survey" in this forum - someone has uploaded a survey db somewhere, as far as I recollect.

Again, same "kind" of data is in the same column dedicated to that "kind", not one column per each data item. All questions requiring, say Yes/No, are of the same kind ...etc. Actually, all questions are one kind "Question", and may have a type, for each type of answers (like Yes/No, a numerical value etc) .. and then the answers are stored in a table, each tagged with QuestionID, UserID - perhaps a separate table for multiple choices answers.. This is one of the cases where any thinking done before coding will save a lot of effort.

 

[byTimber]

Microsoft Access database specifications
Access database
Number of fields in a recordset 255

That's where I got the info from. My need is this:
Fields in a table (container)
UserID; Text1;Text2;Yes/No;Text3,Text4,Yes/No etc to 500 fields

So for the answers beyond the field size maximum (e.g.Text400) to the questions I need another table - don't I?
Best wishes, Roger..

 

[spikepl]

Did you actually read #4 ?

I would suggest you read it very carefully, and follow up on the suggestions there.