Skip to Content

Wiki source code of Data Structures

Version 12.4 by Kerem Yollu on 2025/03/23 10:47
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 === Employee ===
88
89 * employee_role
90 * employee_status
91 * employee_departement (this one i am not shure, maybe departments should be in company ?)
92
93 {{code language="sql"}}
94 DO $$
95 DECLARE
96 tname TEXT;
97 BEGIN
98 FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type',
99 'task_status', 'task_type', 'task_category',
100 'user_role', 'user_type',
101 'project_type','project_areas', 'project_person_roles', 'project_company_role']
102 LOOP
103 EXECUTE format('
104 CREATE TABLE IF NOT EXISTS public.%I (
105 uid SERIAL PRIMARY KEY,
106 name VARCHAR(100) NOT NULL,
107 description VARCHAR(255),
108 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
109 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
110 );
111 ALTER TABLE public.%I OWNER TO kpm_rw;
112 ', tname, tname);
113 END LOOP;
114 END$$;
115 {{/code}}
116
117 == ==