Skip to Content

Changes for page Data Structures

Last modified by Kerem Yollu on 2025/03/23 11:28
From version 12.3
edited by Kerem Yollu
on 2025/03/23 10:46
Change comment: There is no comment for this version
To version 1.1
edited by Kerem Yollu
on 2025/03/21 15:17
Change comment: There is no comment for this version

Summary

Details

Page properties
Parent
... ... @@ -1,1 +1,1 @@
1 -xwiki:Kynsight.Projects.KPM.Data Structures.Data Structure And Models.WebHome
1 +Kynsight.Projects.KPM.Data Structure And Models.WebHome
Content
... ... @@ -1,30 +1,25 @@
1 1  = Data Structures for KPM and database relations =
2 2  
3 3  
4 -= Core Entity Tables =
4 += Models =
5 5  
6 6  == Address ==
7 7  
8 -{{diagram/}}
8 +DROP TABLE IF EXISTS public.address;
9 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
10 +CREATE TABLE public.address
16 16  (
17 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
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)
28 28  )
29 29  
30 30  TABLESPACE pg_default;
... ... @@ -34,84 +34,3 @@
34 34  
35 35  COMMENT ON TABLE public.address
36 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_depar
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 -== ==