Mapping multipart external Identifiers into internal identifier
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)
eg:
products( i1)
products(i2)
party_id_components(p11,party1)
party_id_components(p12,party1)
party_id_components(p13,party1)
party_id_components(p21,party2)
party_id_components(p22,party2)
party_id_mapping(p11,partyid1,i1,idpartvalue11)
party_id_mapping(p12,partyid1,i1,idpartvalue12)
party_id_mapping(p13,partyid1,i1,idpartvalue13)
party_id_mapping(p21,partyid2,i2,idpartvalue21)
party_id_mapping(p22,partyid2,i2,idpartvalue21)
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 .
eg:
products(i1)
products(i2)
party_id_components(p11$p12$p13,party1)
party_id_components(p21$p22,party2)
party_id_mapping(p11$p12$p13,party1,i1,idpartvalue11$idpartvalue12$idpartvalue13)
party_id_mapping(p21$p22,party2,i2,idpartvalue21$idpartvalue22)
Does anyone knows a better solution - something like use a good hash function etc... ?
Software/Hardware used:
Software/Hardware used:
ASKED:
June 23, 2010 11:57 AM
UPDATED:
June 23, 2010 3:49 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
I’m not sure I understand your proposed design.
Does the multi-part external identifier identify a party or a product ? If the latter, why are you relating the parts to the party in your party_id_components table ?
Can a party have more than one product ?
Are you going to need to manipulate or query the parts of a multi-part identifier separately ?
Why don’t you provide a more elaborated example with actual values (i.e. numbers and/or strings) instead of p11, p12, etc ?