# Naming a second additional column referencing another table column

naming convention

I'm working with a table in database that references to a column in another table.
Such as:

Product Table
------------
Id
Description
Currencies_Code

Currencies
------------
Id
Code
Description


We have the convention:

"Foreign key fields should be a combination of the name of the referenced table and the name of the referenced fields."

MyColumn: ExternalTableName_ColumnName

Until there, everything is fine. Now, a second column should be added referencing to Currencies table "Code" column for a second scenario where another currency should be used.

My initial idea was using these columns:

Currencies_Code_Internal

Currencies_Code_External

However, the suffix Internal/External breaks the convention.

Which way of naming would be the most appropiate in this case to change the convention?

You may consider that the breaking of the convention is actually indicative of an error or inefficiency in the design:

For example,

I would have a lookup table for this instead of two occurences of the same foreign key field in a table.

Product Table
------------
Id
Description

Product_Currencies Table
------------
Product_Id
Currencies_Id
Condition_Id

Condition Table
-----------------------
Id
Description

Currencies
------------
Id
Code
Description


In effect, the Product_Currencies table formalizes the relationship : Given this condition, use this currency for this product.

The condition table might contain values like:

Condition
ID     Description
----------------
1      Internal
2      External


The naming convention being broken may be a symptom of a problematic design- many conventions are partially intended to cause issues when violated.

Having two Currencies_Code in one table implies conditional use, which may imply another relation is necessary to describe it.

If you must have an answer to the original question:

It's totally and entirely dependent on who enforces and/or codifies your standard.