Database schema

schema.sql

CREATE TYPE valueType AS ENUM ('Integer', 'Float', 'Long', "String", "Options", "Scale", "Boolean" );

CREATE Type locationType As ENUM ('Registration', 'Healthcoach');

create TABLE Question
(
  id SERIAL,
  sectionId integer,
  title varying(1000),
  subtitle varying(1000),
  helptext varying(1000),
  defaultValue varyin(1000),
  require boolean default false,
  unit varying(255),
  type valueType,
  createdAt timestamp NOT NULL default CURRENT_TIMESTAMP,
  updatedAt timestamp NOT NULL default CURRENT_TIMESTAMP,
);

create TABLE Options {
  id SERIAL NOT NULL,
  questionId integer NOT NULL references Question(id),
  isSelect boolean,
  multi boolean,
  defaultOptionId Integer,
}

create TABLE Option {
  id SERIAL NOT NULL,
  optionsId integer NOT NULL references Options(id),
  key varchar(255) NOT NULL,
  value varchar(255) NOT NULL
}

create TABLE Scale {
  id SERIAL NOT NULL,
  questionId integer NOT NULL references Question(id),
  minLabel varchar(255),
  maxLabel varchar(255),
  increment varchar(255)
}

create Table Label {
  id SERIAL NOT NULL,
  scaleId integer NOT NULL references Scale(id),
  key varchar(255) NOT NULL,
  minValue decimal(10,4) NOT NULL,
  maxValue decimal(10,4) NOT NULL
}

create TABLE Section {
  id SERIAL,
  OnboardingId integer,
  title varying(1000),
  subtitle varying(1000)
}

create TABLE QuestionSectionOrder {
  questionId integer Not null references Question(id),
  sectionId integer Not null references Section(id),
  order integer
}

create TABLE Onboarding { 
  id SERIAL,
  location locationType,
  version varying(10)
}

create TABLE OnboardingSectionOrder {
  onboardinId integer Not null references Onboarding(id),
  sectionId integer Not null references Section(id),
  order integer
}