11

I am looking for advice on the best relational modelling approach to store surveys, questions, and responses.

I am looking for which of the two approaches below looks best, or an alternative approach to either.

I have at least these entities:

  • question
  • survey
  • person

And at least these relationships:

  • Each survey has 1 or more questions.
  • Each question may be used in 0 or more surveys.
  • Each person may take 0 or more surveys.

Here is where I run into trouble: how to model the responses to survey questions made by a person.

Here are two approaches I've considered, neither of which seems very good to me. The diagrams here are greatly simplified to illustrate the issue.

Approach 1: Approach 1

What I don't like about this approach:

  • The survey_person_question_response table has two different columns that refer to a survey: survey_question_survey_id and survey_person_survey_id
    • It would be an error to have different survey_id's referenced in one row for these two columns. The survey_question must be from the same survey as the person took in survey_person. I can't see a good way to enforce this.
  • It seems like what I am doing here is making a relationship between two relationships. That feels wrong to me for some reason.

Approach 2:

Try to avoid two FKs from approach 1 that should refer to the same value... enter image description here

What I don't like about this approach:

  • There is no enforcement that the question_id and survey_id FKs are from a valid survey_question pair
  • There is no enforcement that the survey_id and person_id FKs are from a valid survey_person pair

Any advice on:

  • Whether one of these approaches is a typical approach
  • The pros and cons of one of these approaches over the other
  • A better way to arrange this data entirely

Would be greatly appreciated!

10

As per my understanding of your specifications, your business environment involves a conceptual-level ternary relationship. In this regard, you need to define:

  1. the relationship (or association) type between the entity types Person and Survey;
  2. the relationship type between Survey and Question;
  3. the relationship type that establishes the connection between the two aforesaid relationship types and, as a consequence, between Person, Survey and Question, i.e., Response (a shorter name that simplifies interpretation, from my point of view).

So, I consider that you are on the right track with your Approach 1, although it requires some small (yet important) refinements in order to make it more accurate. I will detail such refinements and other relevant considerations in the following sections.

Business rules

Let us expand the applicable business rules a bit and reformulate them in the following way:

  • A Person registers in zero-one-or-many Surveys
  • A Survey gets the registration of zero-one-or-many Persons
  • A Survey is integrated by one-to-many Questions
  • A Question integrates zero-one-or-many Surveys
  • A Question receives zero-one-or-many Responses
  • A Response is provided by exactly-one Person in the context of exactly-one Survey

Expository IDEF1X diagram

Then, I have created the IDEF1Xa diagram that is presented in Figure 1, which synthesizes the business rules formulated above:

Fig.1 Simplified Survey IDEF1X


a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on theoretical work authored by the sole founder of the relational model, i.e., Dr. E. F. Codd and also on the entity-relationship view developed by Dr. P. P. Chen.


The PersonSurvey relationship

As I see it, the PersonSurvey relationship is required to provide a means of authorization so that a Person can take part in a given Survey. In this way, once a certain Person has been registered in a specific Survey, he or she is authorized to provide Responses to the Questions that integrate the respective Survey.

The SurveyQuestion relationship

I assume that the property (or attribute) called suvery_question.question_number in your diagram is used to represent the Order of presentation of a given Question instance with respect to a particular Survey. As you can see, I have denoted such property as SurveyQuestion.PresentationOrder, and I think that you should prevent that (i) two or more Question.QuestionNumber values share (ii) the same PresentationOrder value in (iii) the same SurveyQuestion occurrence.

To portray that need, I have included a composite ALTERNATE KEY (AK) in the box representing this entity type, which is comprised of the combination of properties (SurveyNumber, QuestionNumber, PresentationOrder). As you are well aware, a composite AK can be declared in a logical DDL design with the aid of a multi-column UNIQUE constraint (as I exemplified in the SurveyQuestion table that is part of the expository DDL layout expounded a few sections below).

The Response entity type

Yes, with the Response entity type I am depicting a relationship between two other relationships; it may seem awkward at first glance but there is nothing wrong with this approach, as long as it (a) represents the features of the business context of interest accurately and (b) is represented properly in a logical-level layout.

Yes, you are totally correct, it would be an error to portray that part of the scenario at the logical level of abstraction by means of two Response.SurveyNumber (or, say, Response.SurveyId) values referenced from two different columns in the same Response row.

Derived logical SQL-DDL layout

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on your business context characteristics.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions.

CREATE TABLE Person (
    PersonId        INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    BirthDate       DATE     NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Person_PK PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK UNIQUE      (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    )
);

CREATE TABLE Survey (
    SurveyNumber    INT       NOT NULL,
    Description     CHAR(255) NOT NULL,
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Survey_PK PRIMARY KEY (SurveyNumber),
    CONSTRAINT Survey_AK UNIQUE      (Description)
);

CREATE TABLE PersonSurvey (
    PersonId           INT      NOT NULL,
    SurveyNumber       INT      NOT NULL,
    RegisteredDateTime DATETIME NOT NULL,
    --
    CONSTRAINT PersonSurvey_PK         PRIMARY KEY (PersonId, SurveyNumber),
    CONSTRAINT PersonSurveyToPerson_FK FOREIGN KEY (PersonId)
        REFERENCES Person (PersonId),
    CONSTRAINT PersonSurveyToSurvey_FK FOREIGN KEY (SurveyNumber)
        REFERENCES Survey (SurveyNumber)
);

CREATE TABLE Question (
    QuestionNumber  INT       NOT NULL,
    Wording         CHAR(255) NOT NULL,
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Question_PK PRIMARY KEY (QuestionNumber),
    CONSTRAINT Question_AK UNIQUE      (Wording)
);

CREATE TABLE SurveyQuestion (
    SurveyNumber       INT      NOT NULL,
    QuestionNumber     INT      NOT NULL,
    PresentationOrder  TINYINT  NOT NULL,
    IsMandatory        BIT      NOT NULL,
    IntegratedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT SurveyQuestion_PK PRIMARY KEY (SurveyNumber, QuestionNumber),
    CONSTRAINT SurveyQuestion_AK UNIQUE      (
        QuestionNumber,
        SurveyNumber,
        PresentationOrder
    ),
    CONSTRAINT SurveyQuestionToSurvey_FK   FOREIGN KEY (SurveyNumber)
        REFERENCES Survey   (SurveyNumber),
    CONSTRAINT SurveyQuestionToQuestion_FK FOREIGN KEY (QuestionNumber)
        REFERENCES Question (QuestionNumber)
);

CREATE TABLE Response (
    SurveyNumber     INT      NOT NULL,
    QuestionNumber   INT      NOT NULL,
    PersonId         INT      NOT NULL,
    Content          TEXT     NOT NULL,
    ProvidedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Response_PK                 PRIMARY KEY (SurveyNumber, QuestionNumber, PersonId),
    CONSTRAINT ResponseToPersonSurvey_FK   FOREIGN KEY (PersonId, SurveyNumber)
        REFERENCES PersonSurvey   (PersonId, SurveyNumber),
    CONSTRAINT ResponseToSurveyQuestion_FK FOREIGN KEY (SurveyNumber, QuestionNumber)
        REFERENCES SurveyQuestion (SurveyNumber, QuestionNumber)
);

Two composite FOREIGN KEYs in the Response table

This is, probably, the most important point to discuss: the references made from a given Response row to

  1. SurveyQuestion.SurveyNumber, and
  2. SurveyPerson.SurveyNumber

must have matching values. As far as I am concerned, the best option to enforce this condition in a declarative way is by making use of two composite FOREIGN KEYs (FKs).

As shown in the DDL design, the first FK is making a reference to the PersonSurvey table PRIMARY KEY (PK), i.e., (PersonId, SurveyNumber), and is conformed by the columns Response.PersonId and Response.SurveyNumber.

The second FK is pointing to the SurveyQuestion table PK, i.e., (SurveyNumber, QuestionNumber), and is, accordingly, made up of the columns Response.SurveyNumber and Response.QuestionNumber.

In this way, the Response.SurveyNumber column is quite instrumental since it is used as part of a FK reference in two different constraints.

With this method, one ensures database management system-guaranteed referential integrity from

  • (a) Response to the PersonSurvey;
  • (b) Response to the SurveyQuestion; and
  • (c) each of the tables representing an associative entity type to the tables standing for independent entity types, namely Person, Survey and Question.

Derived data to avoid update anomalies

I have noticed in your diagram two elements that I esteem are worth mentioning. These elements are related to two PersonSurvey columns that can (should) be derived.

In that regard, you can derive the PersonSurvey.IsStarted datum by querying if a given Person occurrence has provided one or more Responses to Questions that integrate an exact Survey via the SurveyQuestion table.

And you can also obtain the PersonSurvey.IsCompleted data point by determining if a given Person instance has supplied a Response to all the Questions that cointain a value of 'TRUE' in the IsMandatory column in a specific SurveyQuestion row.

By way of the derivation of these values, you are preventing some update anomalies that would have eventually arisen in case you had kept such values in the SurveyQuestion column.

Important consideration

As @Dave rightly points out in his comment, if you face a future requirement demanding the management of different kinds of responses that imply managing dates, numeric values, multiple choice, and another possible aspects, you will have to extend this database layout.

  • 1
    Wow, this perfectly answered the question in my head and then taught me more! Since comments should suggest improvements: It was slightly confusing that keys ended with both ID and Number, but otherwise this is fantastic. Thank you. – Zach Mierzejewski Oct 23 '18 at 14:46
  • @Zach You're most welcome, I'm glad the post helped you. Thanks for the feedback, some refinements are decidedly called for. – MDCCL Oct 23 '18 at 15:26
1

This is one reason why I don't like to prefix columns when migrating them as foreign keys. In your first case, the modeling tool may force you to prefix one of the survey_id columns in the survey_person_question_response table. You may be able to adjust this after the relationship is made.

If necessary remove the redundant survey id field when you build the physical model where you don't need the duplicated column. As you have identified, both your models have issues, but I believe the first model is better overall.

  • Thanks for the insight - I did collapse down to 1 column in the physical model which enforces everything I wanted. – deadcode Nov 6 '15 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.