1 CREATE TABLE survey (

2 id SERIAL4 PRIMARY KEY,

3 title TEXT NOT NULL,

4 text TEXT NOT NULL,

5 finished_title TEXT NOT NULL,

6 finished_text TEXT NOT NULL,

7 created TIMESTAMPTZ NOT NULL DEFAULT now(),

8 open_from TIMESTAMPTZ NOT NULL,

9 open_until TIMESTAMPTZ NOT NULL

10 );

11

12 CREATE TABLE survey_question (

13 id SERIAL4 PRIMARY KEY,

14 survey_id INT4 NOT NULL REFERENCES survey(id),

15 position INT4 NOT NULL,

16 question TEXT NOT NULL,

17 description TEXT,

18 answer_type TEXT NOT NULL,

19 answer_options json

20 );

21

22 CREATE TABLE survey_answer_set (

23 ident TEXT NOT NULL PRIMARY KEY,

24 survey_id INT4 NOT NULL REFERENCES survey(id),

25 data JSON

26 );

27

28 CREATE TABLE survey_answer (

29 id SERIAL8 PRIMARY KEY,

30 survey_answer_set_ident TEXT NOT NULL REFERENCES survey_answer_set(ident) ON DELETE CASCADE,

31 survey_question_id INT4 NOT NULL REFERENCES survey_question(id),

32 answer TEXT NOT NULL

33 );

34

35 CREATE TABLE survey_member (

36 id SERIAL4 PRIMARY KEY,

37 survey_id INT4 NOT NULL REFERENCES survey(id),

38 member_id INT4 NOT NULL REFERENCES member(id),

39 survey_answer_set_ident TEXT REFERENCES survey_answer_set(ident),

40 started TIMESTAMPTZ DEFAULT now(),

41 finished TIMESTAMPTZ,

42 rejected TIMESTAMPTZ

43 );

44

45 INSERT INTO survey (id, title, text, finished_text, open_from, open_until) VALUES (

46 1,

47 'Example survey',

48 'This is just an example',

49 '<strong>Done!</strong><br>You finished it. Thank you very much.',

50 '2021-10-06 12:00',

51 '2021-10-14 12:00'

52 );

53

54 INSERT INTO survey_question (survey_id, position, question, answer_type, answer_options) VALUES

55 (1, 1, 'What color do you like?', 'radio', '[ "Red", "Green", "Blue" ]'),

56 (1, 2, 'What form do you like?', 'radio', '[ "Square", "Circle", "Hexagon" ]')

57 ;

58

59