Wiki source code of Data Structures

Version 13.3 by Kerem Yollu on 2025/03/23 11:26

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
13.3	8	Address table is made so that i can be populated from external source like [[open address,>>https://openaddresses.io/]]
13.2	9
13.3	10	It will be then used from Companies, Users and Persons tables
	11
12.1	12	{{diagram/}}
	13
13.1	14	== Lookup Tables without references. ==
3.2	15
11.3	16	=== Company ===
	17
	18	* company_legal_form
	19	* company_relation
11.4	20	* company_status
11.5	21	* company_types
11.3	22
11.5	23	=== Project ===
	24
11.7	25	* project_areas
	26	* project_type
	27	* project_company_role
	28	* projects_person_role
11.5	29
11.7	30	=== Task ===
	31
11.9	32	* task_category
	33	* task_type
	34	* task_status
11.7	35
11.9	36	=== User ===
11.7	37
11.9	38	* user_role
	39	* user_type
11.7	40
12.2	41	=== Employee ===
	42
	43	* employee_role
	44	* employee_status
12.4	45	* employee_departement (this one i am not shure, maybe departments should be in company ?)
12.2	46
10.1	47	{{code language="sql"}}
	48	DO $$
	49	DECLARE
	50	tname TEXT;
	51	BEGIN
13.1	52	FOREACH tname IN ARRAY ARRAY'TBALE_ONE','TABLE_TWO']
10.1	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
13.1	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}}

Wiki source code of Data Structures

Applications

Navigation