Wiki source code of Data Structures

Last modified by admin admin admin on 2025/11/09 15:16

version	line-number	content
1.1	1	= Data Structures for KPM and database relations =
	2
	3
	4	= Core Entity Tables =
	5
	6	== Address ==
	7
	8	Address table is made so that i can be populated from external source like [[open address,>>https://openaddresses.io/]]
	9
	10	It will be then used from Companies, Users and Persons tables
	11
	12	{{diagram/}}
	13
	14	== Lookup Tables without references. ==
	15
	16	=== Company ===
	17
	18	* company_legal_form
	19	* company_relation
	20	* company_status
	21	* company_types
	22
	23	=== Project ===
	24
	25	* project_areas
	26	* project_type
	27	* project_company_role
	28	* projects_person_role
	29
	30	=== Task ===
	31
	32	* task_category
	33	* task_type
	34	* task_status
	35
	36	=== User ===
	37
	38	* user_role
	39	* user_type
	40
	41	=== Employee ===
	42
	43	* employee_role
	44	* employee_status
	45	* employee_departement (this one i am not shure, maybe departments should be in company ?)
	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}}
	67
	68	== Lookup Tables with references. ==
	69
	70	=== Company ===
	71
	72	* company_departements
	73
	74	{{code language="sql"}}
	75	CREATE TABLE IF NOT EXISTS public.TABLE
	76	(
	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),
	81	creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	82	updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	83	)
	84	TABLESPACE pg_default;
	85
	86	ALTER TABLE IF EXISTS public.TABLE
	87	OWNER TO kpm_rw;
	88	{{/code}}