Best design approach

[Beemerang]

Hi All

I'm really stuck and I've tried this from more than one angle:

(See the attached graphic)
I have a list of survey questions that I need to enter collected responses for into a database. These questions all relate to a school. I have a school table and a survey table. Some of the survey questions may require the school table records to be updated but most relate to general question about the school that are stored in the survey table.

What would be the best way to allow the user to capture the survey answers but also allow any records in the school table to be appropriately updated? So, in this graphic, all fields on the school details tab should update the school table while all other fields should update the survey table.

Any advice would be appreciated.

Beem

 

[Typ0]

What is the survey about? Some of your information is not very clear.

It looks like all of the stuff on the form in the graphic is attributes of the school. So each attribute would be a field in the school table as you say.

I'm going to go off for a minute and tell you what I think...but it may be beyond what you are really asking because i don't know what you mean by a survey.

You want to have a questions table that defines your questions with a primary key.

And then a survey table with it's own primary key and tuples that identify surveys.

Then you want to have an intersect table that brings the question IDs together with the SurveyIDs.

Then you will want to have a user table (this might just be the school if the school is the thing that is doing the responses)

then you are going to have a Responses table that brings together the UserID (or schoolID) with the foreign key of the intersect tables primary key.

Then you are going to handle the survey questions in one fashion and the schools updating their information in the school table another. You could have two different forms but you wouldn't need too...you could easily set off a section of questions programmatically to just update the school attributes.

Not sure if all that made sense...try and make an ERD yourself and see what you come up with.