Wiki source code of Data Structures
Version 11.3 by Kerem Yollu on 2025/03/22 14:59
Hide last authors
| author | version | line-number | content |
|---|---|---|---|
 |
1.1 | 1 | = Data Structures for KPM and database relations = |
| 2 | |||
| 3 | |||
| 4 | = Models = | ||
| 5 | |||
| |
2.2 | 6 | == Address == |
| |
1.1 | 7 | |
| |
11.2 | 8 | {{code language="SQL"}} |
| 9 | -- Table: public.address | ||
| |
9.1 | 10 | |
| |
11.2 | 11 | -- DROP TABLE IF EXISTS public.address; |
| 12 | |||
| 13 | CREATE TABLE IF NOT EXISTS public.address | ||
| |
1.1 | 14 | ( |
| 15 | uid SERIAL PRIMARY KEY, | ||
| |
11.2 | 16 | country character(2) COLLATE pg_catalog."default" NOT NULL, |
| 17 | region character varying(100) COLLATE pg_catalog."default", | ||
| 18 | city character varying(100) COLLATE pg_catalog."default" NOT NULL, | ||
| 19 | postcode character varying(20) COLLATE pg_catalog."default" NOT NULL, | ||
| 20 | street character varying(255) COLLATE pg_catalog."default" NOT NULL, | ||
| 21 | "number" character varying(20) COLLATE pg_catalog."default", | ||
| 22 | unit character varying(50) COLLATE pg_catalog."default", | ||
| 23 | extra_address character varying(255) COLLATE pg_catalog."default", | ||
| 24 | lon double precision, | ||
| 25 | lat double precision | ||
| |
1.1 | 26 | ) |
| 27 | |||
| 28 | TABLESPACE pg_default; | ||
| 29 | |||
| 30 | ALTER TABLE IF EXISTS public.address | ||
| 31 | OWNER to kpm_rw; | ||
| 32 | |||
| 33 | COMMENT ON TABLE public.address | ||
| |
11.2 | 34 | IS 'addresses table, instead of making use of addresses for ever company, customer and co... Prefer to make addresses table and then assign it to a company or a user etc.'; |
| |
3.2 | 35 | |
| |
11.2 | 36 | COMMENT ON COLUMN public.address.uid |
| 37 | IS 'Unique ID for each address'; | ||
| 38 | |||
| 39 | COMMENT ON COLUMN public.address.country | ||
| 40 | IS 'ISO 3166-1 alpha-2 (e.g., ''CH'')'; | ||
| 41 | |||
| 42 | COMMENT ON COLUMN public.address.region | ||
| 43 | IS 'State or canton'; | ||
| 44 | |||
| 45 | COMMENT ON COLUMN public.address.unit | ||
| 46 | IS 'Apartment/Suite/Floor/etc.'; | ||
| 47 | |||
| 48 | COMMENT ON COLUMN public.address.extra_address | ||
| 49 | IS 'Any additional info (optional)'; | ||
| 50 | |||
| 51 | COMMENT ON COLUMN public.address.lon | ||
| 52 | IS 'Optional: for geolocation'; | ||
| 53 | |||
| 54 | COMMENT ON COLUMN public.address.lat | ||
| 55 | IS 'Optional: for geolocation'; | ||
| 56 | {{/code}} | ||
| 57 | |||
| |
11.1 | 58 | == Common Stand alone tables having the same Cols. and pointing to no other table == |
| |
3.2 | 59 | |
| |
11.3 | 60 | === Company === |
| 61 | |||
| 62 | * company_legal_form | ||
| 63 | * company_relation | ||
| 64 | * company_stat | ||
| 65 | |||
| |
10.1 | 66 | {{code language="sql"}} |
| 67 | DO $$ | ||
| 68 | DECLARE | ||
| 69 | tname TEXT; | ||
| 70 | BEGIN | ||
| 71 | FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type', | ||
| 72 | 'task_status', 'task_type', 'task_category', | ||
| 73 | 'user_role', 'user_type', | ||
| 74 | 'project_type','project_areas', 'project_person_roles', 'project_company_role'] | ||
| 75 | LOOP | ||
| 76 | EXECUTE format(' | ||
| 77 | CREATE TABLE IF NOT EXISTS public.%I ( | ||
| 78 | uid SERIAL PRIMARY KEY, | ||
| 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 | ALTER TABLE public.%I OWNER TO kpm_rw; | ||
| 85 | ', tname, tname); | ||
| 86 | END LOOP; | ||
| 87 | END$$; | ||
| 88 | {{/code}} | ||
| 89 | |||
| |
9.1 | 90 | == == |