Wiki source code of Data Structures
Version 10.1 by Kerem Yollu on 2025/03/22 14:48
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 | |
| |
9.1 | 8 | * {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address; |
| 9 | |||
| |
1.1 | 10 | CREATE TABLE public.address |
| 11 | ( | ||
| 12 | uid SERIAL PRIMARY KEY, | ||
| |
2.1 | 13 | country CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 (e.g., 'CH') |
| 14 | region VARCHAR(100), -- State or canton | ||
| |
1.1 | 15 | city VARCHAR(100) NOT NULL, |
| 16 | postcode VARCHAR(20) NOT NULL, | ||
| 17 | street VARCHAR(255) NOT NULL, | ||
| |
2.1 | 18 | number VARCHAR(20), |
| 19 | unit VARCHAR(50), -- Apartment/Suite/Floor/etc. | ||
| 20 | extra_address VARCHAR(255), -- Additional info | ||
| 21 | lon DOUBLE PRECISION, -- Longitude (optional) | ||
| 22 | lat DOUBLE PRECISION -- Latitude (optional) | ||
| |
1.1 | 23 | ) |
| 24 | |||
| 25 | TABLESPACE pg_default; | ||
| 26 | |||
| 27 | ALTER TABLE IF EXISTS public.address | ||
| 28 | OWNER to kpm_rw; | ||
| 29 | |||
| 30 | COMMENT ON TABLE public.address | ||
| |
2.2 | 31 | 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.';{{/code}} |
| |
3.2 | 32 | |
| |
9.1 | 33 | == Common table having the same Cols. an pointing to no other table == |
| |
3.2 | 34 | |
| |
10.1 | 35 | {{code language="sql"}} |
| 36 | DO $$ | ||
| 37 | DECLARE | ||
| 38 | tname TEXT; | ||
| 39 | BEGIN | ||
| 40 | FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type', | ||
| 41 | 'task_status', 'task_type', 'task_category', | ||
| 42 | 'user_role', 'user_type', | ||
| 43 | 'project_type','project_areas', 'project_person_roles', 'project_company_role'] | ||
| 44 | LOOP | ||
| 45 | EXECUTE format(' | ||
| 46 | CREATE TABLE IF NOT EXISTS public.%I ( | ||
| 47 | uid SERIAL PRIMARY KEY, | ||
| 48 | name VARCHAR(100) NOT NULL, | ||
| 49 | description VARCHAR(255), | ||
| 50 | creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | ||
| 51 | updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP | ||
| 52 | ); | ||
| 53 | ALTER TABLE public.%I OWNER TO kpm_rw; | ||
| 54 | ', tname, tname); | ||
| 55 | END LOOP; | ||
| 56 | END$$; | ||
| 57 | {{/code}} | ||
| 58 | |||
| |
9.1 | 59 | == == |