CVP SQL gist

CREATE TABLE Org
(
  id INT NOT NULL AUTO_INCREMENT,
  marketplace VARCHAR NOT NULL,
  created_at DATE NOT NULL,
  updated_at DATE NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE Surveyor
(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR NOT NULL,
  type VARCHAR NOT NULL,
  created_at DATE NOT NULL,
  updated_at DATE NOT NULL,
  org_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (org_id) REFERENCES Org(id)
);

CREATE TABLE Survey
(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR NOT NULL,
  priority INT NOT NULL,
  created_at DATE NOT NULL,
  updated_at DATE NOT NULL,
  status VARCHAR NOT NULL,
  surveyor_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (surveyor_id) REFERENCES Surveyor(id)
);

CREATE TABLE SurveyCampaign
(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR NOT NULL,
  start_time DATE NOT NULL,
  end_time DATE NOT NULL,
  created_at DATE NOT NULL,
  updated_at DATE NOT NULL,
  survey_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (survey_id) REFERENCES Survey(id)
);

CREATE TABLE Question
(
  id INT NOT NULL AUTO_INCREMENT,
  question VARCHAR NOT NULL,
  parent_question_id INT,
  created_at DATE NOT NULL,
  updated_at DATE NOT NULL,
  fcap_ttl INT NOT NULL,
  question_type VARCHAR NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE Answer
(
  id INT NOT NULL AUTO_INCREMENT,
  created_at DATE NOT NULL,
  updated_at DATE NOT NULL,
  answer VARCHAR NOT NULL,
  question_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (question_id) REFERENCES Question(id)
);

CREATE TABLE survey_question_mapping
(
  question_id INT NOT NULL,
  survey_id INT NOT NULL,
  FOREIGN KEY (question_id) REFERENCES Question(id),
  FOREIGN KEY (survey_id) REFERENCES Survey(id)
);

CREATE TABLE page_question_mapping
(
  page_type VARCHAR NOT NULL,
  question_id INT NOT NULL,
  FOREIGN KEY (question_id) REFERENCES Question(id)
);

CREATE TABLE store_question_mapping
(
  store_path VARCHAR NOT NULL,
  question_id INT NOT NULL,
  FOREIGN KEY (question_id) REFERENCES Question(id)
);

CREATE TABLE am_segments_include
(
  segment_id VARCHAR NOT NULL,
  question_id INT NOT NULL,
  FOREIGN KEY (question_id) REFERENCES Question(id)
);

CREATE TABLE am_segments_exclude
(
  segment_id VARCHAR NOT NULL,
  question_id INT NOT NULL,
  FOREIGN KEY (question_id) REFERENCES Question(id)
);