CODEWITHSUNDEEP
c#sql
azazaz
azazaz

Reputation: 35

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.

Upvotes: 1

Views: 108

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29629

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

Upvotes: 3

tadman
tadman

Reputation: 211560

Create a secondary table with the meanings of these integer codes. There's nothing that compels you to JOIN that in, but if you need to that data is there. Within your C# code you can still use an enum to look things up but try to keep that in sync with what's in the database, or vice-versa. One of those should be authoritative.

In practice you'll often find that short strings are easier to work with than rigid enums. In the 1990s when computers were slow and disk space scarce you had to do things like this to get reasonable performance. Now it's not really an issue even on tables with hundreds of millions of rows.

Upvotes: 0

Related Questions