Wiki source code of Data Structures

Version 11.11 by Kerem Yollu on 2025/03/22 15:10

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
11.2	8	{{code language="SQL"}}
	9	-- Table: public.address
9.1	10
11.2	11	-- DROP TABLE IF EXISTS public.address;
	12
	13	CREATE TABLE IF NOT EXISTS public.address
1.1	14	(
	15	uid SERIAL PRIMARY KEY,
11.2	16	country character(2) COLLATE pg_catalog."default" NOT NULL,
	17	region character varying(100) COLLATE pg_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) COLLATE pg_catalog."default",
	22	unit character varying(50) COLLATE pg_catalog."default",
	23	extra_address character varying(255) COLLATE pg_catalog."default",
	24	lon double precision,
	25	lat double precision
1.1	26	)
	27
	28	TABLESPACE pg_default;
	29
	30	ALTER TABLE IF EXISTS public.address
	31	OWNER to kpm_rw;
	32
	33	COMMENT ON TABLE public.address
11.2	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.';
3.2	35
11.2	36	COMMENT ON COLUMN public.address.uid
	37	IS 'Unique ID for each address';
	38
	39	COMMENT ON COLUMN public.address.country
	40	IS 'ISO 3166-1 alpha-2 (e.g., ''CH'')';
	41
	42	COMMENT ON COLUMN public.address.region
	43	IS 'State or canton';
	44
	45	COMMENT ON COLUMN public.address.unit
	46	IS 'Apartment/Suite/Floor/etc.';
	47
	48	COMMENT ON COLUMN public.address.extra_address
	49	IS 'Any additional info (optional)';
	50
	51	COMMENT ON COLUMN public.address.lon
	52	IS 'Optional: for geolocation';
	53
	54	COMMENT ON COLUMN public.address.lat
	55	IS 'Optional: for geolocation';
	56	{{/code}}
	57
11.11	58	== Reference / Lookup Tables ==
3.2	59
11.3	60	=== Company ===
	61
	62	* company_legal_form
	63	* company_relation
11.4	64	* company_status
11.5	65	* company_types
11.3	66
11.5	67	=== Project ===
	68
11.7	69	* project_areas
	70	* project_type
	71	* project_company_role
	72	* projects_person_role
11.5	73
11.7	74	=== Task ===
	75
11.9	76	* task_category
	77	* task_type
	78	* task_status
11.7	79
11.9	80	=== User ===
11.7	81
11.9	82	* user_role
	83	* user_type
11.7	84
10.1	85	{{code language="sql"}}
	86	DO $$
	87	DECLARE
	88	tname TEXT;
	89	BEGIN
	90	FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type',
	91	'task_status', 'task_type', 'task_category',
	92	'user_role', 'user_type',
	93	'project_type','project_areas', 'project_person_roles', 'project_company_role']
	94	LOOP
	95	EXECUTE format('
	96	CREATE TABLE IF NOT EXISTS public.%I (
	97	uid SERIAL PRIMARY KEY,
	98	name VARCHAR(100) NOT NULL,
	99	description VARCHAR(255),
	100	creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	101	updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	102	);
	103	ALTER TABLE public.%I OWNER TO kpm_rw;
	104	', tname, tname);
	105	END LOOP;
	106	END$$;
	107	{{/code}}
	108
9.1	109	== ==

Wiki source code of Data Structures

Applications

Navigation