There are many rows of data in a database table, each of which contains many columns. ID (numeric - 12345, 12378, etc) Name 1 - AAA,BBB,CCC Name 2 - dd, ee, ff Name 3 - Ggg, Hhh, Iii
The objective is to come up with rules based on a combination of these values to map to a set of groups. Group ID 1 - 1,000
There is a large mapping table that applies rules to the individual records and maps each one to it's group ID. The code is spaghetti and has gaps and duplications, and I would like a better approach if you can suggest one.
Here is a small example:
if Name 1 in (...) then use this process below:
if ID = 856743 THEN Group ID = 2000
if ID in (1,2,3... 200) THEN Group ID = 2001
if ID = 377 OR Name 2 in (X,Y,Z) THEN Group ID = 2003
if Name 2 in (U, V, W) THEN Group ID = 2007
Else Group ID = 2011
I am thinking that I need to take all the column permutations and make them dimensions. The problem is there are many segments of mapping like this: if a column value is in (X,Y,Z) and the ID is one of (a large set) then the value is ...
From time to time more IDs are added, and who knows if new column values are added.
Should I just have a mapping table listing: ID, and each dimension, and what the Group ID is? That would necessitate adding new mapping rows when new IDs are added. The current process sometimes handled that automatically because it just takes a record that matches Name 1 and Name 2,
ID N1 N2 Group ID 1 1 1 10 2 1 1 11 2 1 2 12
- 2 1 13
- 2 2 14