In the previous part of these series we build a view generator for multiple properties. Quite analogues to that is the work we do in this part of the series.

There is only one correct way of presenting many-to-many relations and that is MD_RELATION table. But MD_RELATION column names are quite ambiguous to a novice user or system being integrated, so the sole purpose of such a view would be add semantic labels to the columns and objects.

Continuing with materialization of object type $ObjectType with object type code $ObjectTypeCd:

FOR EACH $MultRelation IN 
SELECT
	relType1.relation_type_cd,
	relType1.relation_type_nm,
	relType1.related_relation_type_nm,
	objType1.object_type_nm,
	objType2.object_type_nm as related_object_type_cd
FROM
	MD_RELATION_TYPE relType1
JOIN
	MD_OBJECT_TYPE objType1
	ON objType1.object_type_cd = relType1.object_type_cd
JOIN
	MD_OBJECT_TYPE objType2
	ON objType2.object_type_cd = relType1.related_object_type_cd
WHERE
	relType1.object_type_cd = $ObjectTypeCd
	AND relType1.multiplicity_ind = 1

We build view:

CREATE VIEW v_$MultRelation.relation_type_nm_$MultRelation.related_relation_type_nm AS
SELECT
	rel1.object_id AS $MultRelation.object_type_nm_id,
	rel1.related_object_id AS $MultRelation.related_object_type_cd,
	rel1.changed_dt,
	rel1.changed_by
FROM
	MD_RELATION rel1
WHERE
	relation_type_cd = $MultRelation.relation_type_cd
	AND state_ind = 1

In the next, and the last part of the series, we will look at what we need to know to build an automated process for materializing all or part of MMX metadata.