Skip to Content

Wiki source code of Data Structures

Version 13.1 by Kerem Yollu on 2025/03/23 10:54
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 == Lookup Tables without references. ==
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'TBALE_ONE','TABLE_TWO']
99 LOOP
100 EXECUTE format('
101 CREATE TABLE IF NOT EXISTS public.%I (
102 uid SERIAL PRIMARY KEY,
103 name VARCHAR(100) NOT NULL,
104 description VARCHAR(255),
105 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
106 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
107 );
108 ALTER TABLE public.%I OWNER TO kpm_rw;
109 ', tname, tname);
110 END LOOP;
111 END$$;
112 {{/code}}
113
114 == Lookup Tables with references. ==
115
116 === Company ===
117
118 * company_departements
119
120 {{code language="sql"}}
121 CREATE TABLE IF NOT EXISTS public.TABLE
122 (
123 uid SERIAL PRIMARY KEY,
124 OTHER_TABLE_uid INTEGER NOT NULL REFERENCES public.OTHER_TABLE(uid) ON DELETE CASCADE,
125 name VARCHAR(100) NOT NULL,
126 description VARCHAR(255),
127 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
128 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
129 )
130 TABLESPACE pg_default;
131
132 ALTER TABLE IF EXISTS public.TABLE
133 OWNER TO kpm_rw;
134 {{/code}}