azazaz azazaz - 27 days ago 10
C# Question

SQL good practices and foreign key

I have to create a database structure. I have a question about foreing keys and good practice:

I have a table which must have a field that can be two different string values, either "A" or "B".
It cannot be anything else (therefore, i cannot use a string type field).

What is the best way to design this table:

1) create an int field which is a foreign key to another table with just two records, one for the string "A" and one for the string "B"

2) create an int field then, in my application, create an enumeration such as this

public enum StringAllowedValues
{
A = 1,
B
}


3) ???

In advance, thanks for your time.

Edit: 13 minutes later and I get all this awesome feedback. Thank you all for the ideas and insight.

Answer

Many database engines support enumerations as a data type. And there are, indeed, cases where an enumeration is the right design solution.

However...

There are two requirements which may decide that a foreign key to a separate table is better.

The first is: it may be necessary to increase the number of valid options in that column. In most cases, you want to do this without a software deployment; enumerations are "baked in", so in this case, a table into which you can write new data is much more efficient.

The second is: the application needs to reason about the values in this column, in ways that may go beyond "A" or "B". For instance, "A" may be greater/older/more expensive than "B", or there is some other attribute to A that you want to present to the end user, or A is short-hand for something.

In this case, it is much better to explicitly model this as columns in a table, instead of baking this knowledge into your queries.

In 30 years of working with databases, I personally have never found a case where an enumeration was the right decision....

Comments