How I could assign a preset set of string values to a field of table in SQL Server 2008?

Question

I want a field in a table of SQL Server 2008 to contain a preset number of string values for user to be able then to select one value from a dropdown listbox in my WPF front-end app.

Is it possible only through assigning a numeric int type in a SQL Server table field and then doing all the stuff through C# front-end coding, or are there other ways?

Answer 1

Make a table containing your possible values and reference the field to that table with a FOREIGN KEY:

CREATE TABLE possible_strings (string_val NVARCHAR(100) PRIMARY KEY)

INSERT
INTO    possible_strings
VALUES  ('First value of the list')

INSERT
INTO    possible_strings
VALUE   ('Second value of the list')

ALTER TABLE mytable
ADD CONSTRAINT fk_mytable_mystring_possible
FOREIGN KEY (mystring)
REFERENCES possible_strings (string_val)

To display all possible values in a drop down list, just fill it with the results of this query:

SELECT  string_val
FROM    possible_strings

Answer 2

There are two different ways you could do this (well, two that come to my mind). Neither are specific to Sql Server.

First, is to create a table which holds the values that go into the dropdown. This would be a very simple table such as

DayOfWeek
Name varchar(9) PK

and then reference it in other tables via a foreign key relationship

Hours
Id UniqueIdentifier PK
Day varchar(9) FK
...

The other way is to define an enum in your application

public enum DayOfWeek { Monday, Tuesday, Wednesday, /*etc*/ }

and then save this as an int in your database (yes, you could use a different data type):

Hours
Id UniqueIdentifier PK
Day Int
...

If you expect the list to fluctuate at all, the first technique is better. If you expect the list to be static, the second one is usually easier to deal with in code.

Answer 3

I have done what you are thinking by creating a lookup table in the database and a corresponding Enum type in C#. The danger is that your Enum and the database table will eventually get out of sync when the code is modified. Also, Enum types aren't going to let you use nice readable names in your app.

I have solved this in the past by creating a class that loads the data from the database and caches it. You can bind your UI controls to it as a data source to make things easy on the front-end devs.