Skip to Content

Changes for page Data Structures

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

Summary

Details

Page properties
Parent
... ... @@ -1,1 +1,1 @@
1 -Kynsight.Projects.KPM.Data Structure And Models.WebHome
1 +xwiki:Kynsight.Projects.KPM.Data Structures.Data Structure And Models.WebHome
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 -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;
... ... @@ -29,3 +29,78 @@
29 29  
30 30  COMMENT ON TABLE public.address
31 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.';
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 +== ==