Changes for page Data Structures
Last modified by Kerem Yollu on 2025/03/23 11:28
From version 13.3
edited by Kerem Yollu
on 2025/03/23 11:26
on 2025/03/23 11:26
Change comment:
There is no comment for this version
To version 7.2
edited by Kerem Yollu
on 2025/03/21 16:21
on 2025/03/21 16:21
Change comment:
There is no comment for this version
Summary
-
Page properties (1 modified, 0 added, 0 removed)
Details
- Page properties
-
- Content
-
... ... @@ -1,88 +1,194 @@ 1 1 = Data Structures for KPM and database relations = 2 2 3 3 4 -= CoreEntity Tables =4 += Models = 5 5 6 +* ((( 6 6 == Address == 8 +))) 9 +** {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address; 7 7 8 -Address table is made so that i can be populated from external source like [[open address,>>https://openaddresses.io/]] 11 +CREATE TABLE public.address 12 +( 13 + uid SERIAL PRIMARY KEY, 14 + country CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 (e.g., 'CH') 15 + region VARCHAR(100), -- State or canton 16 + city VARCHAR(100) NOT NULL, 17 + postcode VARCHAR(20) NOT NULL, 18 + street VARCHAR(255) NOT NULL, 19 + number VARCHAR(20), 20 + unit VARCHAR(50), -- Apartment/Suite/Floor/etc. 21 + extra_address VARCHAR(255), -- Additional info 22 + lon DOUBLE PRECISION, -- Longitude (optional) 23 + lat DOUBLE PRECISION -- Latitude (optional) 24 +) 9 9 10 - It will be then used fromCompanies,Users andPersons tables26 +TABLESPACE pg_default; 11 11 12 -{{diagram/}} 28 +ALTER TABLE IF EXISTS public.address 29 + OWNER to kpm_rw; 13 13 14 -== Lookup Tables without references. == 31 +COMMENT ON TABLE public.address 32 + 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}} 15 15 16 -=== Company === 17 17 18 -* company_legal_form 19 -* company_relation 20 -* company_status 21 -* company_types 22 22 23 -=== Project === 36 +* ((( 37 +== Company_Type == 38 +))) 39 +** {{code language="sql"}}-- Table: public.company_type 24 24 25 -* project_areas 26 -* project_type 27 -* project_company_role 28 -* projects_person_role 41 +-- DROP TABLE IF EXISTS public.company_type; 29 29 30 -=== Task === 43 +CREATE TABLE IF NOT EXISTS public.company_type 44 +( 45 + uid SERIAL PRIMARY KEY, 46 + name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL, 47 + description VARCHAR(255) COLLATE pg_catalog."default", 48 + creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 49 + updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP 50 +) 51 +TABLESPACE pg_default; 31 31 32 -* task_category 33 -* task_type 34 -* task_status 53 +ALTER TABLE IF EXISTS public.company_type 54 + OWNER TO kpm_rw; 35 35 36 -=== User === 56 +COMMENT ON TABLE public.company_type 57 + IS 'Reference table for different types of companies (e.g., manufacturer, distributor, partner, etc.). Used by company table.'; 37 37 38 - *user_role39 - *user_type59 +COMMENT ON COLUMN public.company_type.uid 60 + IS 'Unique ID for each company type'; 40 40 41 -=== Employee === 62 +COMMENT ON COLUMN public.company_type.name 63 + IS 'Short name of the company type (e.g., Manufacturer, EMS, Partner)'; 42 42 43 -* employee_role 44 -* employee_status 45 -* employee_departement (this one i am not shure, maybe departments should be in company ?) 65 +COMMENT ON COLUMN public.company_type.description 66 + IS 'Optional longer description of the company type'; 46 46 47 -{{code language="sql"}} 48 -DO $$ 49 -DECLARE 50 - tname TEXT; 51 -BEGIN 52 - FOREACH tname IN ARRAY ARRAY'TBALE_ONE','TABLE_TWO'] 53 - LOOP 54 - EXECUTE format(' 55 - CREATE TABLE IF NOT EXISTS public.%I ( 56 - uid SERIAL PRIMARY KEY, 57 - name VARCHAR(100) NOT NULL, 58 - description VARCHAR(255), 59 - creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 60 - updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP 61 - ); 62 - ALTER TABLE public.%I OWNER TO kpm_rw; 63 - ', tname, tname); 64 - END LOOP; 65 -END$$; 66 -{{/code}} 68 +COMMENT ON COLUMN public.company_type.creation_date 69 + IS 'Timestamp when the entry was created'; 67 67 68 -== Lookup Tables with references. == 71 +COMMENT ON COLUMN public.company_type.updated_date 72 + IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}} 73 +* ((( 74 +== Company_status == 69 69 70 -= ==Company===76 +* {{code language="sql"}}-- Table: public.company_status 71 71 72 - *company_departements78 +-- DROP TABLE IF EXISTS public.company_status; 73 73 74 -{{code language="sql"}} 75 -CREATE TABLE IF NOT EXISTS public.TABLE 80 +CREATE TABLE IF NOT EXISTS public.company_status 76 76 ( 77 77 uid SERIAL PRIMARY KEY, 78 - OTHER_TABLE_uid INTEGER NOT NULL REFERENCES public.OTHER_TABLE(uid) ON DELETE CASCADE, 79 - name VARCHAR(100) NOT NULL, 80 - description VARCHAR(255), 83 + name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL, 84 + description VARCHAR(255) COLLATE pg_catalog."default", 81 81 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 82 82 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP 83 83 ) 84 84 TABLESPACE pg_default; 85 85 86 -ALTER TABLE IF EXISTS public. TABLE90 +ALTER TABLE IF EXISTS public.company_status 87 87 OWNER TO kpm_rw; 88 -{{/code}} 92 + 93 +COMMENT ON TABLE public.company_status 94 + IS 'Reference table for the status or lifecycle stage of a company (e.g., Active, Inactive, Prospect). Used by the company table.'; 95 + 96 +COMMENT ON COLUMN public.company_status.uid 97 + IS 'Unique ID for each company status entry'; 98 + 99 +COMMENT ON COLUMN public.company_status.name 100 + IS 'Short name of the status (e.g., Active, Archived, Lead)'; 101 + 102 +COMMENT ON COLUMN public.company_status.description 103 + IS 'Optional description for the company status'; 104 + 105 +COMMENT ON COLUMN public.company_status.creation_date 106 + IS 'Timestamp when the entry was created'; 107 + 108 +COMMENT ON COLUMN public.company_status.updated_date 109 + IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}} 110 + 111 + 112 + 113 +---- 114 + 115 +== Company == 116 +))) 117 +* ((( 118 +== relation == 119 + 120 +* {{code language="sql"}}-- Table: public.company_relation 121 + 122 +-- DROP TABLE IF EXISTS public.company_relation; 123 + 124 +CREATE TABLE IF NOT EXISTS public.company_relation 125 +( 126 + uid SERIAL PRIMARY KEY, 127 + name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL, 128 + description VARCHAR(255) COLLATE pg_catalog."default", 129 + creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 130 + updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP 131 +) 132 +TABLESPACE pg_default; 133 + 134 +ALTER TABLE IF EXISTS public.company_relation 135 + OWNER TO kpm_rw; 136 + 137 +COMMENT ON TABLE public.company_relation 138 + IS 'Reference table for types of relationships a company may have (e.g., customer, partner, supplier). Used by the company table.'; 139 + 140 +COMMENT ON COLUMN public.company_relation.uid 141 + IS 'Unique ID for each company relation type'; 142 + 143 +COMMENT ON COLUMN public.company_relation.name 144 + IS 'Short name of the relation type (e.g., Customer, Supplier, Partner)'; 145 + 146 +COMMENT ON COLUMN public.company_relation.description 147 + IS 'Optional longer description of the relation type'; 148 + 149 +COMMENT ON COLUMN public.company_relation.creation_date 150 + IS 'Timestamp when the entry was created'; 151 + 152 +COMMENT ON COLUMN public.company_relation.updated_date 153 + IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}} 154 + 155 + 156 +))) 157 +* ((( 158 +== Company_legal_form == 159 + 160 +* {{code language="sql"}}-- Table: public.company_legal_form 161 + 162 +-- DROP TABLE IF EXISTS public.company_legal_form; 163 + 164 +CREATE TABLE IF NOT EXISTS public.company_legal_form 165 +( 166 + uid SERIAL PRIMARY KEY, 167 + name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL, 168 + description VARCHAR(255) COLLATE pg_catalog."default", 169 + creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 170 + updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP 171 +) 172 +TABLESPACE pg_default; 173 + 174 +ALTER TABLE IF EXISTS public.company_legal_form 175 + OWNER TO kpm_rw; 176 + 177 +COMMENT ON TABLE public.company_legal_form 178 + IS 'Reference table for legal structures of companies (e.g., AG, GmbH, Ltd, Inc). Used by the company table.'; 179 + 180 +COMMENT ON COLUMN public.company_legal_form.uid 181 + IS 'Unique ID for each legal form'; 182 + 183 +COMMENT ON COLUMN public.company_legal_form.name 184 + IS 'Short name of the legal form (e.g., GmbH, AG, Ltd, Inc)'; 185 + 186 +COMMENT ON COLUMN public.company_legal_form.description 187 + IS 'Optional longer description of the legal form'; 188 + 189 +COMMENT ON COLUMN public.company_legal_form.creation_date 190 + IS 'Timestamp when the entry was created'; 191 + 192 +COMMENT ON COLUMN public.company_legal_form.updated_date 193 + IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}} 194 +)))