1 CREATE TABLE survey (

2 id SERIAL4 PRIMARY KEY,

3 title TEXT NOT NULL,

4 text TEXT NOT NULL,

5 finished_text TEXT NOT NULL,

6 created TIMESTAMPTZ NOT NULL DEFAULT now(),

7 open_from TIMESTAMPTZ NOT NULL,

8 open_until TIMESTAMPTZ NOT NULL

9 );

10

11 CREATE TABLE survey_question (

12 id SERIAL4 PRIMARY KEY,

13 survey_id INT4 NOT NULL REFERENCES survey(id),

14 position INT4 NOT NULL,

15 question TEXT NOT NULL,

16 description TEXT,

17 answer_type TEXT NOT NULL,

18 answer_options json

19 );

20

21 CREATE TABLE survey_answer_set (

22 ident TEXT NOT NULL PRIMARY KEY,

23 survey_id INT4 NOT NULL REFERENCES survey(id)

24 );

25

26 CREATE TABLE survey_answer (

27 id SERIAL8 PRIMARY KEY,

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

29 survey_question_id INT4 NOT NULL REFERENCES survey_question(id),

30 answer TEXT NOT NULL

31 );

32

33 CREATE TABLE survey_member (

34 id SERIAL4 PRIMARY KEY,

35 survey_id INT4 NOT NULL REFERENCES survey(id),

36 member_id INT4 NOT NULL REFERENCES member(id),

37 survey_answer_set_ident TEXT REFERENCES survey_answer_set(ident),

38 started TIMESTAMPTZ DEFAULT now(),

39 finished TIMESTAMPTZ,

40 rejected TIMESTAMPTZ

41 );

42

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

44 1,

45 'Example survey',

46 'This is just an example',

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

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

49 '2021-10-14 12:00'

50 );

51

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

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

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

55 ;

56

57