Wiki source code of Data Structures
Version 13.2 by Kerem Yollu on 2025/03/23 11:23
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | = Data Structures for KPM and database relations = | ||
| 2 | |||
| 3 | |||
| 4 | = Core Entity Tables = | ||
| 5 | |||
| 6 | == Address == | ||
| 7 | |||
| 8 | Address table is made so that i can be populated from external source like open address, | ||
| 9 | |||
| 10 | {{diagram/}} | ||
| 11 | |||
| 12 | == Lookup Tables without references. == | ||
| 13 | |||
| 14 | === Company === | ||
| 15 | |||
| 16 | * company_legal_form | ||
| 17 | * company_relation | ||
| 18 | * company_status | ||
| 19 | * company_types | ||
| 20 | |||
| 21 | === Project === | ||
| 22 | |||
| 23 | * project_areas | ||
| 24 | * project_type | ||
| 25 | * project_company_role | ||
| 26 | * projects_person_role | ||
| 27 | |||
| 28 | === Task === | ||
| 29 | |||
| 30 | * task_category | ||
| 31 | * task_type | ||
| 32 | * task_status | ||
| 33 | |||
| 34 | === User === | ||
| 35 | |||
| 36 | * user_role | ||
| 37 | * user_type | ||
| 38 | |||
| 39 | === Employee === | ||
| 40 | |||
| 41 | * employee_role | ||
| 42 | * employee_status | ||
| 43 | * employee_departement (this one i am not shure, maybe departments should be in company ?) | ||
| 44 | |||
| 45 | {{code language="sql"}} | ||
| 46 | DO $$ | ||
| 47 | DECLARE | ||
| 48 | tname TEXT; | ||
| 49 | BEGIN | ||
| 50 | FOREACH tname IN ARRAY ARRAY'TBALE_ONE','TABLE_TWO'] | ||
| 51 | LOOP | ||
| 52 | EXECUTE format(' | ||
| 53 | CREATE TABLE IF NOT EXISTS public.%I ( | ||
| 54 | uid SERIAL PRIMARY KEY, | ||
| 55 | name VARCHAR(100) NOT NULL, | ||
| 56 | description VARCHAR(255), | ||
| 57 | creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | ||
| 58 | updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP | ||
| 59 | ); | ||
| 60 | ALTER TABLE public.%I OWNER TO kpm_rw; | ||
| 61 | ', tname, tname); | ||
| 62 | END LOOP; | ||
| 63 | END$$; | ||
| 64 | {{/code}} | ||
| 65 | |||
| 66 | == Lookup Tables with references. == | ||
| 67 | |||
| 68 | === Company === | ||
| 69 | |||
| 70 | * company_departements | ||
| 71 | |||
| 72 | {{code language="sql"}} | ||
| 73 | CREATE TABLE IF NOT EXISTS public.TABLE | ||
| 74 | ( | ||
| 75 | uid SERIAL PRIMARY KEY, | ||
| 76 | OTHER_TABLE_uid INTEGER NOT NULL REFERENCES public.OTHER_TABLE(uid) ON DELETE CASCADE, | ||
| 77 | name VARCHAR(100) NOT NULL, | ||
| 78 | description VARCHAR(255), | ||
| 79 | creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | ||
| 80 | updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP | ||
| 81 | ) | ||
| 82 | TABLESPACE pg_default; | ||
| 83 | |||
| 84 | ALTER TABLE IF EXISTS public.TABLE | ||
| 85 | OWNER TO kpm_rw; | ||
| 86 | {{/code}} |