Naming a second additional column referencing another table column?

Question

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?

Answer 1

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.