In my research, Mapping patterns came in three forms and they varied by levels of complexity. The simplest form was a mapping table that most people recognize as a many to many join. This is easy to use, and easy to understand, but doesn’t allow dynamic mapping. You have to have both entities primary keys and then you have to do an insert to map the relationship. This won’t work on a data store that is constantly receiving new data.
The second form allows dynamic mapping based on attributes. You can set up a process that automatically inserts the rows or assigned the mappings according to each entity’s attribute value. Then you could set up a process where every new entity whose X = Y could be automatically mapped to entity A. This requires columns for each attribute though, and didn’t allow multiple entities.
The third form of mapping table, involves dynamic attributes, dynamic tables, and allows the most flexibility, but is the most difficult to implement and use, which you realize just as soon as you start writing your procs against it.
I happened to inherit a system where the original consultant set up the third form, and the following generations of developers built a variety of complicated tools and complex business processes to maintain it. While it took me a while to understand it, once I did, and realized its strengths, I also realized a simple UI could abstract away the complexity and make it easy to use.
Behind the UI though had to be the data layer, and while the UI was simple looking, the data layer was where all the complexity had to be simplified. Here is one of the queries I wrote to run a mapping function on the fly, for one particular entity, having at least three but no more than four different mapping attributes. This is one of the few times I had to use dynamic SQL in Oracle, whose PL/SQL allowed for great flexibility but would not help me in this case:
procedure MapUnmappedparts(p_certain_date my_part.certain_date%type) is
cursor c_mappings is
select v.id, v.value, v.schema, v.exch_id,
firstkey.attr_name firstname, regexp_substr(v.key, '[^()]+',1,2) firstvalue,
secondkey.attr_name secondname, regexp_substr(v.key, '[^=()]+',1,4) secondvalue,
thirdkey.attr_name thirdname, regexp_substr(v.key, '[^=()]+',1,6) thirdvalue,
fourthkey.attr_name fourthname, regexp_substr(v.key, '[^=()]+',1,8) fourthvalue,
fifthkey.attr_name fifthname, regexp_substr(v.key, '[^=()]+',1,10) fifthvalue,
sixthkey.attr_name sixthname, regexp_substr(v.key, '[^=()]+',1,12) sixthvalue,
seventhkey.attr_name seventhname, regexp_substr(v.key, '[^=()]+',1,14) seventhvalue
from my_values v
join my_attributes firstkey on firstkey.short_name = regexp_substr(v.key, '[^=]+',1,1)
join my_attributes secondkey on secondkey.short_name = regexp_substr(v.key, '[^=()]+',1,3)
join my_attributes thirdkey on thirdkey.short_name = regexp_substr(v.key, '[^=()]+',1,5)
left outer join my_attributes fourthkey on fourthkey.short_name = regexp_substr(v.key, '[^=()]+',1,7)
left outer join my_attributes fifthkey on fifthkey.short_name = regexp_substr(v.key, '[^=()]+',1,9)
left outer join my_attributes sixthkey on sixthkey.short_name = regexp_substr(v.key, '[^=()]+',1,11)
left outer join my_attributes seventhkey on seventhkey.short_name = regexp_substr(v.key, '[^=()]+',1,13)
join my_schemas s on s.sch_id = v.schema and s.key_order = 1
order by s.eval_order, s.sch_id;
v_query varchar2(2000);
begin
--pkg_loadparts.map_pf_codes;
Pkg_somy__Log.writeMessage(p_module => 'Pkg_ProductMapper.MapUnmappedparts',
p_severity => 'DEBUG',
p_message => 'Started');
for r_mapping in c_mappings loop
v_query := 'merge into my_part_Detail d
using (select dd.id, h.co, h.d_type, dd.curve, dd.delivery_point,
dd.curve_2, dd.commodity, h.part_start_month, h.part_end_month,
dd.term_of_trading_cd, h.price_src_1, h.price_src_2, h.term,
dd.vintage, dd.adj_dm_year, h.product_code, dd.region, dd.technology
from my_part_detail dd join my_part_header h on dd.eti = h.eti
where dd.certain_date = :1
and dd.pf_code is null
and nvl(dd.somy__status,''nvl'') <> ''ORI''
) unmapped on (unmapped.id = d.id)
when matched then update set d.pf_code = :2
where 1=1 ';
v_query := v_query || ' and unmapped.' || r_mapping.firstname || ' = :3 ';
v_query := v_query || ' and unmapped.' || r_mapping.secondname || ' = :4 ';
v_query := v_query || ' and unmapped.' || r_mapping.thirdname || ' = :5 ';
if r_mapping.fourthname is not null
then v_query := v_query || ' and unmapped.' || r_mapping.fourthname || ' = :6 ';end if;
if r_mapping.fifthname is not null
then v_query := v_query || ' and unmapped.' || r_mapping.fifthname || ' = :7 ';end if;
if r_mapping.sixthname is not null
then v_query := v_query || ' and unmapped.' || r_mapping.sixthname || ' = :8 ';end if;
if r_mapping.seventhname is not null
then v_query := v_query || ' and unmapped.' || r_mapping.seventhname || ' = :9 ';end if;
case
when r_mapping.seventhname is not null then
execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
r_mapping.secondvalue, r_mapping.thirdvalue, r_mapping.fourthvalue,
r_mapping.fifthvalue, r_mapping.sixthvalue, r_mapping.seventhvalue ;
when r_mapping.sixthname is not null then
execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
r_mapping.secondvalue, r_mapping.thirdvalue, r_mapping.fourthvalue,
r_mapping.fifthvalue, r_mapping.sixthvalue ;
when r_mapping.fifthname is not null then
execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
r_mapping.secondvalue, r_mapping.thirdvalue, r_mapping.fourthvalue,
r_mapping.fifthvalue ;
when r_mapping.fourthname is not null then
execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
r_mapping.secondvalue, r_mapping.thirdvalue, r_mapping.fourthvalue ;
else
execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
r_mapping.secondvalue, r_mapping.thirdvalue ;
end case;
end loop;