Home Forums Database Design Design Ideas and Questions Just an observation: It seems odd that foreign keys cannot be references to unconstrained superkeys...* RE: Just an observation: It seems odd that foreign keys cannot be references to unconstrained superkeys...*

  • January 21, 2017 at 2:21 am

    #1924182

    Further on the schema I posted previously

    Questionnaire
    An instance of a collection of questions and their properties.

    Question
    Individual unique questions and their properties. 

    Questionnaire_question
    Container for the instances of questions belonging to each questionnaire.

    • Unique constraint on questionnaire-question prevents duplicate questions within a questionnaire.
    • Foreign key constraint on Questionnaire prevents orphan questions.
    • Foreign key (non null-able) prevents empty questionnaires.

    Execution
    A header for any instances of collection of answers for any questionnaire.

    • Foreign key constraint to the Questionnaire_question forces the previously listed constraints to be applied to any set of answers.

    Execution_answer
    Container for the answers belonging to each execution.

    • Foreign key constraint to Execution constraints the answer to a specific questionnaire-execution instance
    • Foreign key constraint to Questionnaire_question constraints it to a specific question within the previously constrained Questionnaire-execution collection.