Wiki source code of Data Structures

Version 13.1 by Kerem Yollu on 2025/03/23 10:54

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
13.1	60	== Lookup Tables without references. ==
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
13.1	98	FOREACH tname IN ARRAY ARRAY'TBALE_ONE','TABLE_TWO']
10.1	99	LOOP
	100	EXECUTE format('
	101	CREATE TABLE IF NOT EXISTS public.%I (
	102	uid SERIAL PRIMARY KEY,
	103	name VARCHAR(100) NOT NULL,
	104	description VARCHAR(255),
	105	creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	106	updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	107	);
	108	ALTER TABLE public.%I OWNER TO kpm_rw;
	109	', tname, tname);
	110	END LOOP;
	111	END$$;
	112	{{/code}}
	113
13.1	114	== Lookup Tables with references. ==
	115
	116	=== Company ===
	117
	118	* company_departements
	119
	120	{{code language="sql"}}
	121	CREATE TABLE IF NOT EXISTS public.TABLE
	122	(
	123	uid SERIAL PRIMARY KEY,
	124	OTHER_TABLE_uid INTEGER NOT NULL REFERENCES public.OTHER_TABLE(uid) ON DELETE CASCADE,
	125	name VARCHAR(100) NOT NULL,
	126	description VARCHAR(255),
	127	creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	128	updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	129	)
	130	TABLESPACE pg_default;
	131
	132	ALTER TABLE IF EXISTS public.TABLE
	133	OWNER TO kpm_rw;
	134	{{/code}}

Wiki source code of Data Structures

Applications

Navigation