mapping/translation table design for one to one or multiple to one condition for mapping

Question

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.

Answer 1

May be I'm missing something, but wouldn't simple

create table map (
  group_type varchar(20) not null,
  code_a varchar(20) not null,
  code_b varchar(20) not null
)

do what you want? (group_type, code_a) would be a unique constraint, and the choice of the appropriate index would be dictated by your DBMS abilities.