Maybe not worth the intensive design work for many tables, and it can be a risk to get it wrong, but it's cool when it does work.
Maybe not worth the intensive design work for many tables, and it can be a risk to get it wrong, but it's cool when it does work.
There is a natural PK in the form of the supplier's parts number, but the legacy system (which uses that as PK) notes that it is specifically the parts number as used in the supplier's ERP system, but suppliers often use differently formatted parts numers in publications such as catalogs (adding or removing spaces, slashes or dashes). So the legacy system also has the "published part number" as one or more separate fields.
This inconsistency makes me lean very strongly towards using a synthetic PK instead.
I would concur with adding a synthetic primary key, and maintaining all of the relevant business/domain keys on the same type.
The advantage of this approach is that you can still keep 100% correct relations between your internal types, even if the domain keying scheme is fucked up or otherwise broken per your original assumptions. It's really easy to add a new column to refine the additional domain keys. Fixing a bad PK and all the things that talk to it is much harder.
Or even better: Then the supplier revamps their parts numbering scheme, and every product gets a new number.