I am trying to come up with a flexible design to allow mapping a code or a combination of codes from one system to a single code in another system for a given type of data. For instance for group type 'customer level' codes 'abc' and '123' from system 'A' equal code '5' in system 'B'. Another possible is that group type 'shipping code' code 'xyz' from system 'A' could be a direct mapping to code '99' in system 'B'. I would like to have a flexible design that allows for the multiple conditions to one value and a one to one value mapping without having to create a new table for every condition if possible. Is that possible?
I could just create a flat table that has a column for each system's codes and use a delimiter in the multiple code column, but that seems a bit messy. There will not be hundreds of mappings but probably about 30 or so. Thank you for any assistance in advance.