0

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.

Improve this question
  • 2
    Whatever you do, do not store multiple values in a single column. – mustaccio Sep 6 '18 at 13:42
  • @mustaccio What alternative would you suggest? – Adam Sep 6 '18 at 14:24
  • 1
    Store one value in one column. – mustaccio Sep 6 '18 at 14:44
  • Aye. That’s my goal. Just trying to figure out the best structure to do so. – Adam Sep 6 '18 at 14:50
0

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.

Improve this answer
  • that is kind of what I was thinking as well, but then what would the case be when there is a combination of codes from 'A' that equal code 'B'? Would I have just another row that has the other code then join the table on itself to find both codes? – Adam Sep 6 '18 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.