Wiki source code of Data Structures

Version 10.1 by Kerem Yollu on 2025/03/22 14:48

author	version	line-number	content
		1	= Data Structures for KPM and database relations =
		2
		3
		4	= Models =
		5
		6	== Address ==
		7
		8	* {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address;
		9
		10	CREATE TABLE public.address
		11	(
		12	uid SERIAL PRIMARY KEY,
		13	country CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 (e.g., 'CH')
		14	region VARCHAR(100), -- State or canton
		15	city VARCHAR(100) NOT NULL,
		16	postcode VARCHAR(20) NOT NULL,
		17	street VARCHAR(255) NOT NULL,
		18	number VARCHAR(20),
		19	unit VARCHAR(50), -- Apartment/Suite/Floor/etc.
		20	extra_address VARCHAR(255), -- Additional info
		21	lon DOUBLE PRECISION, -- Longitude (optional)
		22	lat DOUBLE PRECISION -- Latitude (optional)
		23	)
		24
		25	TABLESPACE pg_default;
		26
		27	ALTER TABLE IF EXISTS public.address
		28	OWNER to kpm_rw;
		29
		30	COMMENT ON TABLE public.address
		31	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.';{{/code}}
		32
		33	== Common table having the same Cols. an pointing to no other table ==
		34
		35	{{code language="sql"}}
		36	DO $$
		37	DECLARE
		38	tname TEXT;
		39	BEGIN
		40	FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type',
		41	'task_status', 'task_type', 'task_category',
		42	'user_role', 'user_type',
		43	'project_type','project_areas', 'project_person_roles', 'project_company_role']
		44	LOOP
		45	EXECUTE format('
		46	CREATE TABLE IF NOT EXISTS public.%I (
		47	uid SERIAL PRIMARY KEY,
		48	name VARCHAR(100) NOT NULL,
		49	description VARCHAR(255),
		50	creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		51	updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		52	);
		53	ALTER TABLE public.%I OWNER TO kpm_rw;
		54	', tname, tname);
		55	END LOOP;
		56	END$$;
		57	{{/code}}
		58
		59	== ==