What is the good way of mapping multipart external Identifiers(which is different for different external parties) to an internal Identifier.
for example suppose we have two parties whose identifiers to represent their single product is different.
party1 - ID set (p11, p12, p13)
party2 - Id set (p21,p22)
and we need to map these external identifier with out internal id based on the party.
We can have tables like -
products( internalId(PK), ....)
party_id_components(id part(PK), partyid(PK))
party_id_mapping( idpart(FK), partyid(FK), internalId(FK), idpartvalue)
but this is not a good solution, as making querying to find an internal id given an external id set and v.v will be difficult and performance may hit if for a party the external id consists of many parts.
other solution is combining the parts of the parties with a delimiter and store that .
Does anyone knows a better solution - something like use a good hash function etc... ?