Wiki source code of Data Structures
Last modified by Kerem Yollu on 2025/03/23 11:28
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,>>https://openaddresses.io/]] | ||
| 9 | |||
| 10 | It will be then used from Companies, Users and Persons tables | ||
| 11 | |||
| 12 | {{diagram/}} | ||
| 13 | |||
| 14 | == Lookup Tables without references. == | ||
| 15 | |||
| 16 | === Company === | ||
| 17 | |||
| 18 | * company_legal_form | ||
| 19 | * company_relation | ||
| 20 | * company_status | ||
| 21 | * company_types | ||
| 22 | |||
| 23 | === Project === | ||
| 24 | |||
| 25 | * project_areas | ||
| 26 | * project_type | ||
| 27 | * project_company_role | ||
| 28 | * projects_person_role | ||
| 29 | |||
| 30 | === Task === | ||
| 31 | |||
| 32 | * task_category | ||
| 33 | * task_type | ||
| 34 | * task_status | ||
| 35 | |||
| 36 | === User === | ||
| 37 | |||
| 38 | * user_role | ||
| 39 | * user_type | ||
| 40 | |||
| 41 | === Employee === | ||
| 42 | |||
| 43 | * employee_role | ||
| 44 | * employee_status | ||
| 45 | * employee_departement (this one i am not shure, maybe departments should be in company ?) | ||
| 46 | |||
| 47 | {{code language="sql"}} | ||
| 48 | DO $$ | ||
| 49 | DECLARE | ||
| 50 | tname TEXT; | ||
| 51 | BEGIN | ||
| 52 | FOREACH tname IN ARRAY ARRAY'TBALE_ONE','TABLE_TWO'] | ||
| 53 | LOOP | ||
| 54 | EXECUTE format(' | ||
| 55 | CREATE TABLE IF NOT EXISTS public.%I ( | ||
| 56 | uid SERIAL PRIMARY KEY, | ||
| 57 | name VARCHAR(100) NOT NULL, | ||
| 58 | description VARCHAR(255), | ||
| 59 | creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | ||
| 60 | updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP | ||
| 61 | ); | ||
| 62 | ALTER TABLE public.%I OWNER TO kpm_rw; | ||
| 63 | ', tname, tname); | ||
| 64 | END LOOP; | ||
| 65 | END$$; | ||
| 66 | {{/code}} | ||
| 67 | |||
| 68 | == Lookup Tables with references. == | ||
| 69 | |||
| 70 | === Company === | ||
| 71 | |||
| 72 | * company_departements | ||
| 73 | |||
| 74 | {{code language="sql"}} | ||
| 75 | CREATE TABLE IF NOT EXISTS public.TABLE | ||
| 76 | ( | ||
| 77 | uid SERIAL PRIMARY KEY, | ||
| 78 | OTHER_TABLE_uid INTEGER NOT NULL REFERENCES public.OTHER_TABLE(uid) ON DELETE CASCADE, | ||
| 79 | name VARCHAR(100) NOT NULL, | ||
| 80 | description VARCHAR(255), | ||
| 81 | creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | ||
| 82 | updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP | ||
| 83 | ) | ||
| 84 | TABLESPACE pg_default; | ||
| 85 | |||
| 86 | ALTER TABLE IF EXISTS public.TABLE | ||
| 87 | OWNER TO kpm_rw; | ||
| 88 | {{/code}} |