gistfile1.sql

-- if you have all {Child, Parents, Keys}

function check_relation_type(child_table, child_column, parent_table, parent_column)
begin
	declare child_cardinality double;
	declare parent_cardinality double;
	declare q varchar(255);


	set q = concat('select count(distinct ', child_column ,') / count ( ', child_column, ') into @val from ', child_table , 'where ', child_column , ' is not null');
	exec q;
	set child_cardinality = @val;

	set q = concat('select count(distinct ', parent_column ,') / count ( ', parent_column, ') into @val from ', parent_table , 'where ', parent_column , ' is not null');
	exec q;
	set parent_cardinality = @val;

	if child_cardinality = 1 and parent_cardinality = 1 then
		return 1:1
	end if;

	if child_cardinality = 1 then
		return 1:M
	end if;

	if parent_cardinality = 1 then
		return N:1
	end if;

	-- be aware of M:N
	-- if you have Parent, Child with M:N type it would be (usually) normalized 
	-- in the following way: parent -> parent_child(parent_id, child_id) <- child
	return M:N 

end