Max number of fields in table

[AnnaFoot]

Hello,

I am designing a system for a client who wants to store answers to a survey with over 500 questions, broken into a number of sections.

I have devloped a numbering system so that the fields aren't named by the words of the question, so that each field is immediately identifiable as a given question.

I have read a number of posts here about how if you have more than a certain number of fields in you table things aren't normalised.

But i can't get my head around normailising 500 seperate questions.

I know that i can not have more than 255 fields in one table. So i will definately have to have 2 one to one tables. I'm only on section 3 (of 18) and it takes a phenominal amount of time to save the form. I am wondering if i wouldn't be better off having 18 one to one tables - one for each section. I'm mostly interested in the difference it will make to the speed at which it will operate.

Does anyone have any thoughts or experience with this?

Thanks,
Anna

 

[KenHigg]

Sounds like you need to put questions in seperate table. Do you need help modeling this?

ken

 

[AnnaFoot]

Thanks Ken,

I'm happy doing it, i just wasn't sure whether to have a few tables with 200 or so fields, or more tables with fewer fields, from an efficiency point of view.

Anna

 

[Rich]

You don't need a table with 200 or 500 fields. You would have a table with 200 or 500 records, one for each question. Does that help?

 

[AnnaFoot]

Really?

There are hundreds of each survey - isn't each field a question, and each survey a record?

Anna

 

[KenHigg]

1 question = 1 field = bad idea

ken

 

[Rich]

There have been example Survey dbs posted here, try searching to see if you can find one to give you a better idea of the structure.

You need more than one table for this obviously.

 

[AnnaFoot]

Then perhaps i do need help.

700 + surveys have been carried out, each survey has 500 + questions, many of the questions have a choice of answers, some of them the same answers.

My thoughts were to make the answers to questions be seperate tables, used as a look-up for the relevant questions.

Then the questions would be the fields, so that each survey has it's own record.

If i have a set of tables tied in a one to one relationship, using a unique id, to allow for the fact Access can't have more than 255 fields.

If this is a bad idea, what is a good idea?

Anna

 

[Mile-O]

Read Pat's post here. In fact, read the whole thread.

 

[KenHigg]

Just curious. Was that 700 instances of one survey, Or 700 completly different surveys?

Heed Miles (and Pats), advice, put the effort into it, and you'll be a hero to your end users... And (Big plus here), your db will be scalable - Doing another survey will be a snap...

ken

 

[AnnaFoot]

700 instances of one survey!

Thanks for all the help, i wish i had thought of searching under survey before i posted, to save your energies!

Anna