Wiki source code of Data Structures

Version 12.4 by Kerem Yollu on 2025/03/23 10:47

version	line-number	content
1.1	1	= Data Structures for KPM and database relations =
	2
	3
11.11	4	= Core Entity Tables =
1.1	5
2.2	6	== Address ==
1.1	7
12.1	8	{{diagram/}}
	9
11.2	10	{{code language="SQL"}}
	11	-- Table: public.address
9.1	12
11.2	13	-- DROP TABLE IF EXISTS public.address;
	14
	15	CREATE TABLE IF NOT EXISTS public.address
1.1	16	(
	17	uid SERIAL PRIMARY KEY,
11.2	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
1.1	28	)
	29
	30	TABLESPACE pg_default;
	31
	32	ALTER TABLE IF EXISTS public.address
	33	OWNER to kpm_rw;
	34
	35	COMMENT ON TABLE public.address
11.2	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.';
3.2	37
11.2	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
11.11	60	== Reference / Lookup Tables ==
3.2	61
11.3	62	=== Company ===
	63
	64	* company_legal_form
	65	* company_relation
11.4	66	* company_status
11.5	67	* company_types
11.3	68
11.5	69	=== Project ===
	70
11.7	71	* project_areas
	72	* project_type
	73	* project_company_role
	74	* projects_person_role
11.5	75
11.7	76	=== Task ===
	77
11.9	78	* task_category
	79	* task_type
	80	* task_status
11.7	81
11.9	82	=== User ===
11.7	83
11.9	84	* user_role
	85	* user_type
11.7	86
12.2	87	=== Employee ===
	88
	89	* employee_role
	90	* employee_status
12.4	91	* employee_departement (this one i am not shure, maybe departments should be in company ?)
12.2	92
10.1	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
9.1	117	== ==

Wiki source code of Data Structures

Applications

Navigation