10

What name can you give the surrogate key column in a database table when the convention suggested name collides with that of an existing user field business key?

For example, if I am creating a Product table and ProductId is already used and known by that name in the company, business-wise. A example value of existing ProductId is something like 'PROD-1'.

At this point I'm evaluating going with "ProductKey" as I think naming it simply 'Id' would be confusing.

Chris Herring
  • 231
  • Consider renaming the table? If I'm a business user and I have a ProductID and I see a table called Product, I'm going to start making assumptions... – david25272 Sep 09 '16 at 01:12

3 Answers3

9

If you still have a chance to make this kind of decisions, I heavily recommend you pick a strict naming convention for your surrogate keys like Tablename<suffix>, where <suffix> is a fixed text like "Id" or "_Id" or "SKey", or "_PK" like "primary key". What suffix you choose does not really matter, if you expect naming collisions with some business keys, pick one which avoids this. The goal should be to make it easier to create generic code based on that convention, and to need less documentation because the convention means always the same.

However, you might have already picked a convention, started to build your system around it with some data and applications. Then you extend the system afterwards and find a naming collision with some business key. In this situation it might be easier to pick a different name for the business key column, even if it matches the companies' conventions only to 95% instead of 100%.

Doc Brown
  • 206,877
4

That's called a natural or business key.

In relational model database design, a natural key (also known as business key) is a key that is formed of attributes that already exist in the real world. For example, a US citizen's social security number could be used as a natural key. In other words, a natural key is a candidate key that has a logical relationship to the attributes within that row. A natural key is sometimes called domain key.

Update:

If in your case a business key already exists and is already known as PRODUCT_ID by the business people, a good alternative name for the surrogate I guess you want to create would be PRODUCT_NUMBER or PRODUCT_NUM.

PRODUCT_KEY doesn't look so good a name for me because the other column is also a key, a candidate key. The surrogate will be the primary a key and the natural key will have a unique index based on it, making it a business key that will be used for searches. So calling the surrogate PRODUCT_KEY would be like calling it PRODUCT_PK (slightly better).

I'd go for PRODUCT_NUMBER or PRODUCT_NUM.

Tulains Córdova
  • 39,449
3

If the product Id doesn't change then use the existing business/natural key as the primary key. No need for a surrogate key.

An alternative, if you are still in the design phase of your database, is naming the business key ProductCode and maintaining your convention for the primary key name.

Greg Burghardt
  • 37,083
  • The need for a surrogate key arises typically because of technical reasons like using an ORM, so your initial suggestion is most often not an option. – Doc Brown Aug 27 '16 at 20:31
  • Usually best to use a surrogate key. Even if specifications state the business key won't change we all know that's no guarantee. All using an integer key offers some benefits over a string key. – Chris Herring Aug 27 '16 at 21:13
  • @DocBrown: Most ORMs handle non surrogate keys fine, as well as string keys and composite keys. I building an application now using all types. – Greg Burghardt Aug 28 '16 at 01:01
  • @Herring: While it's true you cant always guarantee a natural key won't change, you can look at the history of the business and existing systems. It's likely you'll get a feel for whether or not you can trust this by looking at other applications in use by the same company. I personally would go for naming the business key something else and keep the surrogate key naming convention. A Product Id can be a name used in the UI. – Greg Burghardt Aug 28 '16 at 01:05
  • @GregBurghardt Surrogate keys are very important in historic data auditing and are particularly useful in data warehouse implementations. I would not recommend using a business key in a data warehouse scenario. We would retain the business key, but also add a surrogate key as the main primary key. There are also performance considerations, as a business key may be too wide to be performant as a clustered index key. Also clustered indexes based on wide non-sequential IDs are not performant for write operations. – Adam Aug 31 '21 at 13:34