Changes for page Data Structures
Last modified by Kerem Yollu on 2025/03/23 11:28
From version 11.8
edited by Kerem Yollu
on 2025/03/22 15:01
on 2025/03/22 15:01
Change comment:
There is no comment for this version
To version 7.1
edited by Kerem Yollu
on 2025/03/21 16:16
on 2025/03/21 16:16
Change comment:
There is no comment for this version
Summary
-
Page properties (1 modified, 0 added, 0 removed)
Details
- Page properties
-
- Content
-
... ... @@ -3,26 +3,24 @@ 3 3 4 4 = Models = 5 5 6 +* ((( 6 6 == Address == 8 +))) 9 +** {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address; 7 7 8 -{{code language="SQL"}} 9 --- Table: public.address 10 - 11 --- DROP TABLE IF EXISTS public.address; 12 - 13 -CREATE TABLE IF NOT EXISTS public.address 11 +CREATE TABLE public.address 14 14 ( 15 15 uid SERIAL PRIMARY KEY, 16 - country character(2)COLLATE pg_catalog."default"NOT NULL,17 - region character varying(100)COLLATEpg_catalog."default",18 - city character varying(100)COLLATE pg_catalog."default"NOT NULL,19 - postcode character varying(20)COLLATE pg_catalog."default"NOT NULL,20 - street character varying(255)COLLATE pg_catalog."default"NOT NULL,21 - "number"character varying(20)COLLATEpg_catalog."default",22 - unit character varying(50)COLLATEpg_catalog."default",23 - extra_address character varying(255)COLLATEpg_catalog."default",24 - lon double precision,25 - lat double precision14 + 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) 26 26 ) 27 27 28 28 TABLESPACE pg_default; ... ... @@ -31,74 +31,160 @@ 31 31 OWNER to kpm_rw; 32 32 33 33 COMMENT ON TABLE public.address 34 - 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 + 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}} 35 35 36 -COMMENT ON COLUMN public.address.uid 37 - IS 'Unique ID for each address'; 38 38 39 -COMMENT ON COLUMN public.address.country 40 - IS 'ISO 3166-1 alpha-2 (e.g., ''CH'')'; 41 41 42 -COMMENT ON COLUMN public.address.region 43 - IS 'State or canton'; 36 +* ((( 37 +== Company_Type == 38 +))) 39 +** {{code language="sql"}}-- Table: public.company_type 44 44 45 -COMMENT ON COLUMN public.address.unit 46 - IS 'Apartment/Suite/Floor/etc.'; 41 +-- DROP TABLE IF EXISTS public.company_type; 47 47 48 -COMMENT ON COLUMN public.address.extra_address 49 - IS 'Any additional info (optional)'; 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; 50 50 51 - COMMENTONCOLUMNpublic.address.lon52 - IS'Optional:forgeolocation';53 +ALTER TABLE IF EXISTS public.company_type 54 + OWNER TO kpm_rw; 53 53 54 -COMMENT ON COLUMN public.address.lat 55 - IS 'Optional: for geolocation'; 56 -{{/code}} 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.'; 57 57 58 -== Common Stand alone tables having the same Cols. and pointing to no other table == 59 +COMMENT ON COLUMN public.company_type.uid 60 + IS 'Unique ID for each company type'; 59 59 60 -=== Company === 62 +COMMENT ON COLUMN public.company_type.name 63 + IS 'Short name of the company type (e.g., Manufacturer, EMS, Partner)'; 61 61 62 -* company_legal_form 63 -* company_relation 64 -* company_status 65 -* company_types 65 +COMMENT ON COLUMN public.company_type.description 66 + IS 'Optional longer description of the company type'; 66 66 67 -=== Project === 68 +COMMENT ON COLUMN public.company_type.creation_date 69 + IS 'Timestamp when the entry was created'; 68 68 69 - *project_areas70 - *project_type71 -* project_company_role72 - *projects_person_role71 +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 == 73 73 74 -= ==Task===76 +* {{code language="sql"}}-- Table: public.company_status 75 75 76 - *78 +-- DROP TABLE IF EXISTS public.company_status; 77 77 80 +CREATE TABLE IF NOT EXISTS public.company_status 81 +( 82 + uid SERIAL PRIMARY KEY, 83 + name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL, 84 + description VARCHAR(255) COLLATE pg_catalog."default", 85 + creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 86 + updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP 87 +) 88 +TABLESPACE pg_default; 78 78 90 +ALTER TABLE IF EXISTS public.company_status 91 + OWNER TO kpm_rw; 79 79 80 -{{code language="sql"}} 81 -DO $$ 82 -DECLARE 83 - tname TEXT; 84 -BEGIN 85 - FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type', 86 - 'task_status', 'task_type', 'task_category', 87 - 'user_role', 'user_type', 88 - 'project_type','project_areas', 'project_person_roles', 'project_company_role'] 89 - LOOP 90 - EXECUTE format(' 91 - CREATE TABLE IF NOT EXISTS public.%I ( 92 - uid SERIAL PRIMARY KEY, 93 - name VARCHAR(100) NOT NULL, 94 - description VARCHAR(255), 95 - creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 96 - updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP 97 - ); 98 - ALTER TABLE public.%I OWNER TO kpm_rw; 99 - ', tname, tname); 100 - END LOOP; 101 -END$$; 102 -{{/code}} 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.'; 103 103 104 -== == 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 +== Company_relation == 113 + 114 +* {{code language="sql"}}-- Table: public.company_relation 115 + 116 +-- DROP TABLE IF EXISTS public.company_relation; 117 + 118 +CREATE TABLE IF NOT EXISTS public.company_relation 119 +( 120 + uid SERIAL PRIMARY KEY, 121 + name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL, 122 + description VARCHAR(255) COLLATE pg_catalog."default", 123 + creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 124 + updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP 125 +) 126 +TABLESPACE pg_default; 127 + 128 +ALTER TABLE IF EXISTS public.company_relation 129 + OWNER TO kpm_rw; 130 + 131 +COMMENT ON TABLE public.company_relation 132 + IS 'Reference table for types of relationships a company may have (e.g., customer, partner, supplier). Used by the company table.'; 133 + 134 +COMMENT ON COLUMN public.company_relation.uid 135 + IS 'Unique ID for each company relation type'; 136 + 137 +COMMENT ON COLUMN public.company_relation.name 138 + IS 'Short name of the relation type (e.g., Customer, Supplier, Partner)'; 139 + 140 +COMMENT ON COLUMN public.company_relation.description 141 + IS 'Optional longer description of the relation type'; 142 + 143 +COMMENT ON COLUMN public.company_relation.creation_date 144 + IS 'Timestamp when the entry was created'; 145 + 146 +COMMENT ON COLUMN public.company_relation.updated_date 147 + IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}} 148 + 149 + 150 +))) 151 +* ((( 152 +== Company_legal_form == 153 + 154 +* {{code language="sql"}}-- Table: public.company_legal_form 155 + 156 +-- DROP TABLE IF EXISTS public.company_legal_form; 157 + 158 +CREATE TABLE IF NOT EXISTS public.company_legal_form 159 +( 160 + uid SERIAL PRIMARY KEY, 161 + name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL, 162 + description VARCHAR(255) COLLATE pg_catalog."default", 163 + creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 164 + updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP 165 +) 166 +TABLESPACE pg_default; 167 + 168 +ALTER TABLE IF EXISTS public.company_legal_form 169 + OWNER TO kpm_rw; 170 + 171 +COMMENT ON TABLE public.company_legal_form 172 + IS 'Reference table for legal structures of companies (e.g., AG, GmbH, Ltd, Inc). Used by the company table.'; 173 + 174 +COMMENT ON COLUMN public.company_legal_form.uid 175 + IS 'Unique ID for each legal form'; 176 + 177 +COMMENT ON COLUMN public.company_legal_form.name 178 + IS 'Short name of the legal form (e.g., GmbH, AG, Ltd, Inc)'; 179 + 180 +COMMENT ON COLUMN public.company_legal_form.description 181 + IS 'Optional longer description of the legal form'; 182 + 183 +COMMENT ON COLUMN public.company_legal_form.creation_date 184 + IS 'Timestamp when the entry was created'; 185 + 186 +COMMENT ON COLUMN public.company_legal_form.updated_date 187 + IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}} 188 +)))