Skip to Content

Changes for page Data Structures

Last modified by Kerem Yollu on 2025/03/23 11:28
From version 10.1
edited by Kerem Yollu
on 2025/03/22 14:48
Change comment: There is no comment for this version
To version 12.2
edited by Kerem Yollu
on 2025/03/23 10:46
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -1,25 +1,30 @@
1 1  = Data Structures for KPM and database relations =
2 2  
3 3  
4 -= Models =
4 += Core Entity Tables =
5 5  
6 6  == Address ==
7 7  
8 -* {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address;
8 +{{diagram/}}
9 9  
10 -CREATE TABLE public.address
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
11 11  (
12 12   uid SERIAL PRIMARY KEY,
13 - country CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 (e.g., 'CH')
14 - region VARCHAR(100), -- State or canton
15 - city VARCHAR(100) NOT NULL,
16 - postcode VARCHAR(20) NOT NULL,
17 - street VARCHAR(255) NOT NULL,
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)
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
23 23  )
24 24  
25 25  TABLESPACE pg_default;
... ... @@ -28,10 +28,63 @@
28 28   OWNER to kpm_rw;
29 29  
30 30  COMMENT ON TABLE public.address
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}}
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.';
32 32  
33 -== Common table having the same Cols. an pointing to no other table ==
38 +COMMENT ON COLUMN public.address.uid
39 + IS 'Unique ID for each address';
34 34  
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_d
92 +
35 35  {{code language="sql"}}
36 36  DO $$
37 37  DECLARE