In the previous part of these series we build a view generator for object materialization that included pointers to parent objects, non-multiple enumeration and text/date/numeric properties and many-to-one relations. What was left out were the multiple properties.

To include multiple properties in the object type based view, we must choose one of the solutions:

  • add as many columns for each property type as there are maximum instances for a single object
  • limit number of instances we materialize for each property type
  • build a separate view for such property types exposing all active properties.

Of these options, for the automated materialization engine only the last one is suitable:

  • having a non-fixed number of columns is extremely bad for integration views
  • and when limiting the number of instances included in the view, we will need some additional storage object to store the materialization settings as MMX model does not provide hosting for such information.

So, continuing with materialization of object type $ObjectType with object type code $ObjectTypeCd

FOR EACH $MultipleProp IN
	SELECT
		prop1.property_type_cd,
		prop1.property_type_nm
	FROM
		MD_OBJECT_TYPE type1
	JOIN
		MD_PROPERTY_TYPE prop1
		ON prop1.object_type_cd = type1.object_type_cd
	WHERE
		type1.object_type_cd = $ObjectTypeCd
 

We create a separate view:

CREATE VIEW $ObjectType_$MultipleProp.property_type_nm AS
	SELECT 
		prop1.property_id,
		obj1.object_id AS $ObjectType_id,
		prop1.property_nm,
		prop1.value_ds as property_value,
		prop1.changed_dt,
		prop1.changed_by
	FROM
		MD_OBJECT obj1
	JOIN
		MD_PROPERTY prop1
		ON prop1.object_id = obj1.object_id
		AND prop1.property_type_cd = $MultipleProp.property_type_cd
	WHERE 
		obj1.object_type_cd = $ObjectTypeCd
		AND obj1.state_ind = 1
 

Alternatively for multiple text properties, if we have modeled the multiplicity only for purposes of saving values in different languages, we can materialize the property values in the main object type view for a chosen default language. But that requires that such assumption is validated in the process of metadata validation prior to exposing data via views.

In next part of the series, we take on materialization of many-to-many relations.