Storing Questionnaire Configurations

[TastyWheat]

I'm building a questionnaire/survey database for my work. Everything seems pretty straightforward, but I'm a little unsure about how to store the survey configurations.

I'm assuming that the survey will change sometime between now and the end of time so I'm tracking survey configurations (which questions and in what order). Right now my "Configuration" table has two fields: ConfigID (Autonumber), Questions (Memo). The 'Questions' field is a space seperated list of question IDs. The other way I could've done it is to use three fields: ConfigID (Long), OrderNum (Int), QuestionID (Long).

I figure the first way is more memory efficient in the long run and it will take much less time to add a new configuration.

The way I'm doing things now works for me, but I'd like to know if anyone thinks I'm setting myself up for trouble down the road or if my way is actually less memory efficient (I'm not as concerned with processing time).

 

[The_Doc_Man]

The way to do this is

tblQuestions - where you have the literal question and a question ID

tblConfigs - where you identify the date on which a new configuration was created and assign a config ID to that date.

tblCQ - where you list the config number and question number to show that question 1 was in config 1 - but question 39 didn't come into play until config 7

I use config ID because it is smaller (by half) than a date field as the key and you can store other data about the config in the tblConfig if that is something you need to do.

 

[TastyWheat]

That definitely sounds like the "proper" way to do it, but for the sake of cutting entry time I'm sticking with my design. The only real advantage I see (when doing it properly) is access time speed up. I have other issues besides that so it's fast enough for my needs.