I have a part table that stores parts data. the table looks like this (simplified version):
table part
id
partcode (this should be unique)
description

information about partcode can come from two or more different sources. let's say vendorACode, vendorBCode and vendorCCode catalogues.
It often happens that user refers to the same part using different part code. Let's say part P is identfied as x01 by vendorACode, y02 by vendorBCode, and z11 by vendorCCode catalogues (names are arbitrary).
So the problem is that user inputs part P using vendorACode catalogue. And another user inputs the same part using vendorBCode catalogue without knowing that data about part P is already stored in the table, resulting in data duplicate.

My immediate answer to this proble is to create another table that acts as an alias (part_alias), where one record in part table maps to one or several records in part_alias (one to many relationship).
How can I solve this problem ?
Any help would be greatly appreciated..

if you want partcode to be unique, all you have to do is ~make~ partcode unique by declaring a UNIQUE constraint on it

problem solved

I'm afraid the solution is not that simple.. what if two unique values of partno refer to the same part (object)..??

you can't have it both ways
AND
either partcode IS unique, or it isn't.
if it isn't then you may need to define another mechansim to make it unique. the only way I can see of doing that is if the partnumber is supplied from one or more suppliers and each supplier uses the same partcode for an equivalent part.

in that event the partcode would still be unique, but you would need a table to identify suppliers and an intersection tabel to associate suppliers with specific partcodes.


incidentally I'm surprised to find an ID column on what you pass off as a unique part, surely the partcode by itself is unique, or are you changing the rules (again), or merely being frugal with the business requirement

This is a standard business requirement. Our ERPS meets it with intersection tables that hold the SKU ID value, customer/supplier ID value and the third party's SKU ID value.

We also get around potential duplication of SKUs by only allowing one department to allocate and enrol new SKUs, but that's more of a business process than a feature of the database.