0

I have a few systems and I need to import data from these systems into a common table. But ID is unique only within source system. So I need to create my own primary key to uniquely identify this data in the target table.

What is best practice to resolve the problem? I don't want to create primary key as character and I don't want to generate an ID for instance by adding 1000000000. Concatenation with name of system will build character data type. Do you have an idea or some solution?

Thanks for help.

APC
  • 144,005
  • 19
  • 170
  • 281
caruzo
  • 127
  • 1
  • 12

2 Answers2

1

Primary keys created with concatenation is not good practice, you are right. It will bring you problems in the future too find out and resolve the keys. The best practice I think its to create the key which consists of 2 attributes like SYSTEMID , ID there ID you can keep old ID from system, the pair of 2 attributes gives you unique PK.

Seyran
  • 711
  • 4
  • 8
1

There is no best practice, there is only opinion. But generally it is a bad idea to use source system keys as primary keys in a data warehouse (or whatever data store you're building).

Create new columns for the target table, at the very least surrogate primary key; something to identify the originating system is very useful too. Populate those when you load the data. You may want to enforce (source system key, source system name ) as a unique key, in which case you will need to add that originating system identifier column.

APC
  • 144,005
  • 19
  • 170
  • 281