What’s the recommended way to handle naming collisions with the PK column?

naming convention

I have a data field "Shipment ID" which is a VARCHAR(18) from a number of different vendors. This is going into a table shipment with a surrogate PK shipment_id.

I'm trying to figure out the best way to name this column. Our conventions say:

  1. Only PKs or FKs can end in _id.
  2. PKs are tablename_id. So a table hello_world would have a PK of hello_world_id.
  3. table and columns are lowercase_with_underscores
  4. nothing about name collisions (if I get a good answer here, I'll make sure it's added to the style guide.)

I know it doesn't follow this awesome answer, but that's what we've got

I can't use the "Shipment ID" field as a PK because it's not guaranteed to be unique or even exist. Plus it is a string and I am not a fan of string PKs.

I don't really like shipment_name or vendor_shipment_id. What do you guys use in cases like this?

Best Answer

Given the stated rules, shipment_id_from_vendor would be better than vendor_shipment_id (doesn't end in _id, so not expected to be a PK/FK field).

Generally, for me, a name collision would mean that one or both names require a better definition, to clarify whey they're distinct values. Even near-collisions should be avoided: If you have a requirements date and a request date, using the names req_date and request_date is not particularly helpful; someone may be looking for the request date, see req_date, and assume that they've found what they wanted. requirements_date may be much longer to type, but it's immediately clear what it is, and no one's going to mix it up with request_date.

That said, there's no universal solution to this. I have no issue with long table and column names, if that's what's needed to make them clear; other people would insist brevity is key and make the two columns from the previous paragraph reqr_date and rqst_date.