|
-- @tag: follow_ups
|
|
-- @description: Tabellenstruktur für Wiedervorlagen und allgemeine Notizen
|
|
-- @depends: release_2_4_3
|
|
CREATE SEQUENCE note_id;
|
|
CREATE TABLE notes (
|
|
id integer NOT NULL DEFAULT nextval('note_id'),
|
|
subject text,
|
|
body text,
|
|
created_by integer NOT NULL,
|
|
|
|
trans_id integer,
|
|
trans_module varchar(10),
|
|
|
|
itime timestamp DEFAULT now(),
|
|
mtime timestamp,
|
|
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY (created_by) REFERENCES employee (id)
|
|
);
|
|
|
|
CREATE TRIGGER mtime_notes
|
|
BEFORE UPDATE ON notes
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE set_mtime();
|
|
|
|
CREATE SEQUENCE follow_up_id;
|
|
CREATE TABLE follow_ups (
|
|
id integer NOT NULL DEFAULT nextval('follow_up_id'),
|
|
follow_up_date date NOT NULL,
|
|
created_for_user integer NOT NULL,
|
|
done boolean DEFAULT FALSE,
|
|
note_id integer NOT NULL,
|
|
created_by integer NOT NULL,
|
|
|
|
itime timestamp DEFAULT now(),
|
|
mtime timestamp,
|
|
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY (created_for_user) REFERENCES employee (id),
|
|
FOREIGN KEY (created_by) REFERENCES employee (id),
|
|
FOREIGN KEY (note_id) REFERENCES notes (id)
|
|
);
|
|
|
|
CREATE TRIGGER mtime_follow_ups
|
|
BEFORE UPDATE ON follow_ups
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE set_mtime();
|
|
|
|
CREATE SEQUENCE follow_up_link_id;
|
|
CREATE TABLE follow_up_links (
|
|
id integer NOT NULL DEFAULT nextval('follow_up_link_id'),
|
|
follow_up_id integer NOT NULL,
|
|
trans_id integer NOT NULL,
|
|
trans_type text NOT NULL,
|
|
trans_info text,
|
|
|
|
itime timestamp DEFAULT now(),
|
|
mtime timestamp,
|
|
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY (follow_up_id) REFERENCES follow_ups (id)
|
|
);
|
|
|
|
CREATE TRIGGER mtime_follow_up_links
|
|
BEFORE UPDATE ON follow_up_links
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE set_mtime();
|
|
|
|
CREATE TABLE follow_up_access (
|
|
who integer NOT NULL,
|
|
what integer NOT NULL,
|
|
|
|
FOREIGN KEY (who) REFERENCES employee (id),
|
|
FOREIGN KEY (what) REFERENCES employee (id)
|
|
);
|