CODEWITHSUNDEEP
databasedelphims-access
Al C
Al C

Reputation: 5377

Can a detail have two masters?

I'm struggling with how I would set up a database for a situation where you have people (non anonymously) filling out questionnaires. The relationship between people and questionnaires is many-to-many--a person completes multiple questionnaires, and a questionnaire is completed by multiple people. So, I have a PERSON table, a QUESTIONNAIRE table, and a join table (PERSON-QUESTIONNAIRE).

But where do I put the info appearing in the questionnaires themselves? Questionnaire items have two parts (two fields), one with a question or statement people will respond to (ITEM) and one with space for the response (RESPONSE). Assuming all this goes in a separate table, what does this table join with? (The QUESTIONNAIRE table ... a QUESTIONNAIRE can have multiple QUESTIONNAIRE-ITEMS?)

And, if I'm correct about that last point, I can't for the life of me figure out how to set up a DB grid (in Delphi) that would display the ITEM and RESPONSE fields for a selected questionnaire and selected person. (I know how to code master/detail relationships with db-aware components, but this is like a detail with two masters.)

Upvotes: 1

Views: 227

Answers (2)

NGLN
NGLN

Reputation: 43669

You will have a table QUESTIONNAIRE-ITEMS (the questions) and a separate table QUESTIONNAIRE-RESPONSES (the answers), because there will be multiple answers for a single question, those from multiple persons. The former gets a foreign key to QUESTIONNAIRE, while the latter gets foreign keys to both QUESTIONNAIRE-ITEMS and PERSON.

In fact, the PERSON-QUESTIONNAIRE table could be omitted, since that information could be queried from the other tables. So for the design I suggest:

  • Surveys: ID (PK), Description, etc...
  • Users: ID (PK), Name, etc...
  • Questions: ID (PK), SurveyID (FK), Question, etc...
  • Answers: ID (PK), QuestionID (FK), UserID (FK), Answer, etc...

The view for the DBGrid will be based on a query like:

SELECT 
  Question, 
  Answer,
  ...
FROM 
  Questions INNER JOIN Answers ON Questions.ID = Answers.QuestionID
WHERE
  Questions.SurveyID = :SurveyID AND Answers.UserID = :UserID

In another part of you GUI, you select the SurveyID and UserID parameters.

And as a bonus: the query to acquire surveys and users, as an alternative to your separate join table, will look like :

SELECT
  Surveys.ID,
  Users.ID
FROM
  Users INNER JOIN (
    Answers INNER JOIN (
      Questions INNER JOIN (
        Surveys
      ) ON Questions.SurveyID = Surveys.ID
    ) ON Answers.QuestionID = Questions.ID
  ) ON Users.ID = Answers.UserID
GROUP BY
  Surveys.ID,
  Users.ID

Upvotes: 7

Alanyst
Alanyst

Reputation: 1410

ITEM and RESPONSE should be different tables; there is a one-to-many relationship between them. (An item belongs to a questionnaire, but many people can provide responses to that item, so each item will have many responses.)

I recommend a setup like this:

PERSON  <---- PERSON_QUESTIONNAIRE ----> QUESTIONNAIRE
                        ^                       ^
                        |                       |
                        |                       |
                     RESPONSE      ----->     ITEM

I don't have any suggestions about how to set up the DB grid (not a Delphi guru) but sometimes getting the design kinks worked out will help the technical solution become clearer.

Upvotes: 3

Related Questions