Hi All,

I'm struggling to work out the best way of modelling some data and thought I'd ask here.

My app contains a survey of a number of questions (55 at the moment). The survey is split into 4 clusters, each cluster has 2 or 3 sectors and each sector has 4 or 5 questions.

i.e. [Cluster] <--->> [Sector] <--->> [Question].

(<--->> = "one-to-many" relation)

The questions are the same for everyone.

Now I need to store users' answers.

This is how I was thinking of modelling it...

[User] <--->> [UserMap] <--->> [QuestionScore].

The UserMap will hold the date created and mapID etc...
The QuestionScore will hold the questionID and the given value for the answer.

However, I was uncertain whether it was a good idea to keep the question content (i.e. the first part of the model) separate from the storage of the answers (i.e. the second part of the model).

Should I have some relation between the two? Or should I integrate them so that the UserMap then stores the Clusters, Sectors and Questions (along with the scores)?

This would mean a lot of duplicated storage though?!

Any input welcome!

Thanks!

I would add some relation otherwise when you pull out an answer you may not be able to be 100% certain what question it was answering (some data structures don't need to output in the same order they were input, I can't remember which ones do or don't atm).

My thought would be

Table 1
List of the questions showing the cluster and sector for each inc a unique ID

Table 2
If needed a list of user details and a unique ID

Table 3
time date stamp, user ID, Question ID , answer

From that you can recreate just about anything.

Thanks both!

Helped a lot in clearing up what I was doing!