0

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?

  • 2
    it's your convention; how can we be expected to make that judgment call for you? – Max Vernon Jun 3 '15 at 22:02
  • in fact, for what Thomas explains below, this violation of convention is caused by a design problem. – John Mayer Jun 3 '15 at 22:28
0

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.

  • 1
    "It's totally and entirely dependent on who enforces and/or codifies your standard" -> that bit. – Max Vernon Jun 3 '15 at 22:03
  • 1
    thank you very much @Thomas, what you explain there is very useful and you give the reasons why wrong design is involved here. It clarifies the scenario a lot. – John Mayer Jun 3 '15 at 22:21

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.