Storing selected option value in Database

Question

I currently have an array I created like this:

$regions=array(
    1=>'North West',
    2=>'North East',
    3=>'South West',
    4=>'South East',
    5=>'West Midlands',
    6=>'East Midlands',
    7=>'London',
    8=>'Yorkshire',
    9=>'East England',
    10=>'Scotland',
    11=>'Wales',
    12=>'N. Ireland',
);

I will use this array to generate dropdown list options (value=>text) in my form. The selected option value will be stored in the database in an integer field.

This might just be a matter of personal preference, but I was just wondering whether this is the correct approach? Or should the array key be the same as its value? Could there be any potential problems further down the line (adding/deleting/modifying the array) when using this approach?

Answer 1

I tend to stored the key value in this instance. However I would normally store the regions in a lookup table where the id and values are stored so any future modifications can be managed relatively easily using foreign keys etc.

Answer 2

This is generally correct, but typically the dropdown list (and thus the array) is generated from the database (at least at some point, even if cached later). This is so that someone doesn't change the code and the change the meaning of the previously stored values.

Answer 3

One of the nice things about MySQL is that it directly supports enum field types. IMHO its infinitely preferable to use significant data over surrogate values.

There is some debate over this in database circles - a quick google turned up about 82000 matches for "relational database enum type". Go read some of them.

But those who disagree with it as a practice unversally recommend maintaining the surrogate/value lookups within the database. NOT in the code.

C.