6

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, bussiness-wise. A exaple 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.

  • 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 9 '16 at 1:12
6

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%.

  • Good points. Agree that a strict naming convention is more important than the name used. – Chris Herring Aug 27 '16 at 21:11
3

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.

  • Whasn't the question "What do you call a key that already exists?" (as opossed to one created artificially). – Tulains Córdova Aug 27 '16 at 2:19
  • @ChrisHerring I also updated. Now that I see you were asking something completelly different than I thought. – Tulains Córdova Aug 27 '16 at 2:40
2

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.

  • 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 1: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 1:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.