plz help: just take a look - i need opinion

[reservedbcreater]

it is a database for housing inpsection surveys.
there are 7 reserves, 100s of hosues for each resever. each survey has a
unique ID(so that the same house inspected in different years would each
have their own survey to compare.)
there are 100s of questions which are catagorized in surveysubjects
table.
each survey must contain all subjects and questions even if the questinos
asked are left blank.

I've used At Your Survey database sample but this is as far as ive got.

What is correct, what needs to be changed, and what are the relationships
i need? any suggestions?

tblInspector
InpsectorID(p.key)
InpsectorName

tblOccupant
OccupantID(p.key)
HouseNumber(p.key)
OccupantName

tblPossibleResponses
QuestionID(p.key)
Responses(p.key)

tblReserve
ResereveID(p.key)
ReserveName

tblResponses
SurveyID
SubjectName
QuestionID
QuestionText
Responses

tblSurveyQuestions
QuestionID(p.key)
SubjectID
SubjectName
QuestionText
ResponseType
LimitList
ResponsesValid
ResponseLength
ResponseDefault

tblSurvey
SurveyID(p.key)
ReserveName
InspectorName
SurveyDate
HouseNumber
OccupantName
PictureLink

tblSurveySubject
SubjectID(p.key)
SubjectName

 

[Ian Davies]

Hi
You will need to explain the connection between the fields you are using.
Readers of your message probably wont know what for example is a reserve and
what relationship it has to a house, and how is a survey related to
questions and a subject etc.
If this is explained in more detail someone could better advise you on the
way you have organised your tables.
From what you have written you may be needing two tables, one containing
surveyID and subjectID only to link tblSurvey and tblSurveySubject, ensuring
each survey can be joined with every subject
and another table containing surveyID and questionID only, to link tblSurvey
to the tblSurveyQuestions in the same way. But I may be wrong as Im not
exactly clear on how all these fields relate to each other

Ian

 

[James A. Fortune]

it is a database for housing inpsection surveys.
there are 7 reserves, 100s of hosues for each resever. each survey has a
unique ID(so that the same house inspected in different years would each
have their own survey to compare.)
there are 100s of questions which are catagorized in surveysubjects
table.
each survey must contain all subjects and questions even if the questinos
asked are left blank.

I've used At Your Survey database sample but this is as far as ive got.

What is correct, what needs to be changed, and what are the relationships
i need? any suggestions?

tblInspector
InpsectorID(p.key)
InpsectorName

tblOccupant
OccupantID(p.key)
HouseNumber(p.key)
OccupantName

tblPossibleResponses
QuestionID(p.key)
Responses(p.key)

tblReserve
ResereveID(p.key)
ReserveName

tblResponses
SurveyID
SubjectName
QuestionID
QuestionText
Responses

tblSurveyQuestions
QuestionID(p.key)
SubjectID
SubjectName
QuestionText
ResponseType
LimitList
ResponsesValid
ResponseLength
ResponseDefault

tblSurvey
SurveyID(p.key)
ReserveName
InspectorName
SurveyDate
HouseNumber
OccupantName
PictureLink

tblSurveySubject
SubjectID(p.key)
SubjectName

I have a few questions that would help me offer some suggestions.

1) What's the difference/relationship between an inspector and a reserve?

2) Are you primarily concerned with preserving historical data (saving
names attached to ID's like a snapshot rather than the ID's)?

3) Are you reasonably sure that the tables related to the survey portion
will handle all your survey questions?

4) Does tblPossibleResponses contain a separate line/record for each
possible response to a particular question?

5) Is the main purpose of the database to archive surveys or compile
statistics?

The problem with a fairly complex table design is that you have to be
able to envision how you're going to use the database to understand
whether or not the table structure will allow you to accomplish your
goals (usually reports).

How about a possible starting point:

tblHouse
HouseID AutoPK
HouseDataFields

tblReserve
ReserveID AutoPK
ReserveName

tblOccupants
OccupantID AutoPK
HouseID fk
OccupantLastName
OccupantFirstName
OccupantMiddleInitial
OccupantDataFields

tblInspector
InspectorID AutoPK
InspectorName

tblSurveySubject
SurveySubjectID AutoPK
SubjectName

tblSurveyQuestions
SQID AutoPK
QuestionID Text
SubjectName
QuestionText
ResponseType
LimitList
ResponsesValid (What's this for?)
ResponseLength
ResponseDefault

tblSurvey
SurveyID AutoPK
ReserveID fk
InspectorID fk
SurveyDate
HouseID fk
OccupantID fk

tblResponses
ResponseID AutoPK
SurveyID fk
SurveySubjectID fk
SQID fk
Responses

tblPossibleResponses
PRID AutoPK
QuestionID Text
Responses (one/record)

The only way to know what's best for you is to imagine the database in
action. Will the above fields enable you to do what you want? If not,
now is the time to adjust the table structure. The problem with a
complex table structure is that you practically have to design the
entire database in your head to know whether or not your table structure
is adequate. So imagine that most of the data is in place and you are
getting ready to put a record into tblSurvey and records into
tblResponses. Hmmm... Do the survey questions ever change? I'd say it
would be better to have another table:

tblSurveyContent
SCID AutoPK
SurveyStyleNumber
QuestionID fk
QuestionPosition

Put SurveyStyleNumber as a foreign key into tblSurvey. This would allow
you to have several different sets of surveys that never get deleted
(for history) but that can have new sets added. That makes putting a
survey together easier and also prevents having to back out the
questions and their order from tblResponses later. You've still got to
do the mental juggling until all the table structures are correct. I
can almost imagine Access being able to do something with it. Note:
I've never looked at 'At Your Survey' so I am unfamiliar with the design
decisions for his table structure and I have not spent much time
thinking about the best way to implement surveys. Hopefully this will
give you a place to start some thoughts or discussions.

James A. Fortune