This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2023-11-25
Channels
does that make sense to you ?
-- this is the detail table
create table surveyjs_model (
id uuid primary key not null,
survey_id bigint not null,
model jsonb not null,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- this is the master table
-- survey that has the last surveyjs model stored as json
-- the idea is :if you update the model field, a new version must be generated on surveyjs_model detail table
-- and its id must be updated on survey.last_model_id
CREATE TABLE survey (
survey_id bigserial PRIMARY KEY,
title VARCHAR(255) NOT NULL,
model json, -- last surveyjs model definition, as json to respect spaces
last_model_id uuid references surveyjs_model, -- point to surveyjs_model that shadows model, but as jsonb
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- why im doing this ? because surveyjs model is a blackbox, we are relying on its structure, its json , its data its good enough
-- the problem is : we need to enable survey.model to be updated, but it can be something totally different than before
-- so we need to support the notion of version. surveyjs_model is the version table. its a detail table that points to
-- survey, it has the model as jsonb, and we take answers we need to correlate the answers with the model that is has been
-- generated from, so we will use surveyjs_model for that.It kinda makes sense - I'm wondering if you could always store the model definition in the surveyjs_model table, and rather than use last_model_id name the column current_model_id (or just model_id)
I worked on something similar, (importing survey results from 3rd party services like Typeform and Qualtrics) and we had to version the survey configuration and also when capturing responses so be able to reconcile them as the survey config changed externally and we were syncing new answers
The end user will not interact with the surveyjs_model table directly. From their perspective, the survey definition resides in the survey table, along with the title and the surveyjs model in JSON format, preserving spaces. This model is created using https://surveyjs.io/create-free-survey, and the user copies the result into the survey table. When this occurs, a new surveyjs_model record is created, and the reference to this new record is maintained in the last_model_id field. The surveyjs_model table is used to track the versions of the survey model. The data type for the model is jsonb, and it has a reference to its master table, survey. This is because when we render a survey, we store the results of each respondent along with the data linking to the surveyjs_model.id. This allows us to record the answers in another table and determine which surveyjs model definition was used to generate those answers