Skip to Content

Wiki source code of Data Structures

Version 12.1 by Kerem Yollu on 2025/03/23 10:14
Show last authors
1 = Data Structures for KPM and database relations =
2
3
4 = Core Entity Tables =
5
6 == Address ==
7
8 {{diagram/}}
9
10 {{code language="SQL"}}
11 -- Table: public.address
12
13 -- DROP TABLE IF EXISTS public.address;
14
15 CREATE TABLE IF NOT EXISTS public.address
16 (
17 uid SERIAL PRIMARY KEY,
18 country character(2) COLLATE pg_catalog."default" NOT NULL,
19 region character varying(100) COLLATE pg_catalog."default",
20 city character varying(100) COLLATE pg_catalog."default" NOT NULL,
21 postcode character varying(20) COLLATE pg_catalog."default" NOT NULL,
22 street character varying(255) COLLATE pg_catalog."default" NOT NULL,
23 "number" character varying(20) COLLATE pg_catalog."default",
24 unit character varying(50) COLLATE pg_catalog."default",
25 extra_address character varying(255) COLLATE pg_catalog."default",
26 lon double precision,
27 lat double precision
28 )
29
30 TABLESPACE pg_default;
31
32 ALTER TABLE IF EXISTS public.address
33 OWNER to kpm_rw;
34
35 COMMENT ON TABLE public.address
36 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.';
37
38 COMMENT ON COLUMN public.address.uid
39 IS 'Unique ID for each address';
40
41 COMMENT ON COLUMN public.address.country
42 IS 'ISO 3166-1 alpha-2 (e.g., ''CH'')';
43
44 COMMENT ON COLUMN public.address.region
45 IS 'State or canton';
46
47 COMMENT ON COLUMN public.address.unit
48 IS 'Apartment/Suite/Floor/etc.';
49
50 COMMENT ON COLUMN public.address.extra_address
51 IS 'Any additional info (optional)';
52
53 COMMENT ON COLUMN public.address.lon
54 IS 'Optional: for geolocation';
55
56 COMMENT ON COLUMN public.address.lat
57 IS 'Optional: for geolocation';
58 {{/code}}
59
60 == Reference / Lookup Tables ==
61
62 === Company ===
63
64 * company_legal_form
65 * company_relation
66 * company_status
67 * company_types
68
69 === Project ===
70
71 * project_areas
72 * project_type
73 * project_company_role
74 * projects_person_role
75
76 === Task ===
77
78 * task_category
79 * task_type
80 * task_status
81
82 === User ===
83
84 * user_role
85 * user_type
86
87 {{code language="sql"}}
88 DO $$
89 DECLARE
90 tname TEXT;
91 BEGIN
92 FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type',
93 'task_status', 'task_type', 'task_category',
94 'user_role', 'user_type',
95 'project_type','project_areas', 'project_person_roles', 'project_company_role']
96 LOOP
97 EXECUTE format('
98 CREATE TABLE IF NOT EXISTS public.%I (
99 uid SERIAL PRIMARY KEY,
100 name VARCHAR(100) NOT NULL,
101 description VARCHAR(255),
102 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
103 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
104 );
105 ALTER TABLE public.%I OWNER TO kpm_rw;
106 ', tname, tname);
107 END LOOP;
108 END$$;
109 {{/code}}
110
111 == ==