1 -- @tag: time_recordings
2 -- @description: Tabellen zur Zeiterfassung
3 -- @depends: release_3_5_6_1
5 CREATE TABLE time_recording_types (
7 abbreviation TEXT NOT NULL,
9 position INTEGER NOT NULL,
10 obsolete BOOLEAN NOT NULL DEFAULT false,
14 CREATE TABLE time_recordings (
16 customer_id INTEGER NOT NULL,
18 start_time TIMESTAMP NOT NULL,
21 description TEXT NOT NULL,
22 staff_member_id INTEGER NOT NULL,
23 employee_id INTEGER NOT NULL,
24 itime TIMESTAMP NOT NULL DEFAULT now(),
25 mtime TIMESTAMP NOT NULL DEFAULT now(),
28 FOREIGN KEY (customer_id) REFERENCES customer (id),
29 FOREIGN KEY (staff_member_id) REFERENCES employee (id),
30 FOREIGN KEY (employee_id) REFERENCES employee (id),
31 FOREIGN KEY (project_id) REFERENCES project (id),
32 FOREIGN KEY (type_id) REFERENCES time_recording_types (id)
35 CREATE TRIGGER mtime_time_recordings BEFORE UPDATE ON time_recordings FOR EACH ROW EXECUTE PROCEDURE set_mtime();