Relationship with optional in-between table I am currently implementing a self-assessment tool. The requirements for its questionnaires are as follows:


  A questionnaire has one or more dimensions and every dimension has either one or more questions or one or more subdimensions (and every subdimension has one or more questions). 


Sub dimensions are irrelevant for the evaluation (while dimensions are relevant) and are only used for separating questions of dimensions, providing introduction texts and categorization inside reports that will be generated after filling out a questionnaire.

When I directly translate those requirements into an ERD, it looks like this:


Or, written out (I left out most irrelevant columns for clarity):

CREATE TABLE IF NOT EXISTS questionnaire
(
    id BIGSERIAL NOT NULL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS dimension
(
    id               BIGSERIAL NOT NULL PRIMARY KEY,
    order_index      SMALLINT  NOT NULL,
    questionnaire_id BIGINT    NOT NULL REFERENCES questionnaire ON DELETE CASCADE,
    UNIQUE (questionnaire_id, order_index)
);


CREATE TABLE IF NOT EXISTS subdimension
(
    id           BIGSERIAL NOT NULL PRIMARY KEY,
    order_index  SMALLINT  NOT NULL,
    dimension_id BIGINT    NOT NULL REFERENCES dimension ON DELETE CASCADE,
    UNIQUE (dimension_id, order_index)
);

CREATE TABLE IF NOT EXISTS question
(
    id              BIGSERIAL NOT NULL PRIMARY KEY,
    order_index     SMALLINT  NOT NULL,
    dimension_id    BIGINT REFERENCES dimension ON DELETE CASCADE,
    subdimension_id BIGINT REFERENCES subdimension ON DELETE CASCADE,
    CHECK ( dimension_id IS NOT NULL AND subdimension_id IS NULL
        OR subdimension_id IS NOT NULL AND dimension_id IS NULL ),
    UNIQUE (dimension_id, order_index),
    UNIQUE (subdimension_id, order_index)
);


While this works, I am not very happy with this solution. For example, if I would want to get all questions of a questionnaire, the best query that comes to my mind would be this:

SELECT q.id
FROM question q
         INNER JOIN dimension d ON q.dimension_id = d.id
WHERE questionnaire_id = 1
UNION
SELECT q.id
FROM question q
         INNER JOIN subdimension s ON q.subdimension_id = s.id
         INNER JOIN dimension d ON s.dimension_id = d.id
WHERE questionnaire_id = 1;


Alternatives that came to my mind: 


always referencing the dimension in the question table (so it won't be an either/or anymore and the CHECK constraint would be obsolete).
always using a subdimension, even though there does not need to be one (if a dimension has only one subdimension it would be some kind of 'dummy' subdimension that would not provide an introduction text etc). This would mean there is no direct reference from a question to a dimension.


Does anyone have any better ideas? I feel like I am missing the obvious.
I think that the idea of a special &ldquo;empty&rdquo; subdimension that is used if there is no real subdimension is the best one. It makes the data model and the queries simple.