3 ms·
Yeah, I'm currently facing exactly that dilemma. I have to model a database of machine parts. There is a natural PK in the form of the supplier's parts number,
by brazzy 5y ago
Yeah, I'm currently facing exactly that dilemma. I have to model a database of machine parts.
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.
- bob1029 5y agoWe deal with a lot of crap where a certain business entity has several different biz keys that can reasonably be used to uniquely identify the thing. 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.
- CRConrad 5y ago> There is a natural PK in the form of the supplier's parts number, but ... Or even better: Then the supplier revamps their parts numbering scheme, and every product gets a new number.