Page 1 of 1

Convert fields with database or in code?

Posted: Fri Dec 15, 2006 2:33 pm
by timclaason
Suppose I've got code values for a particular field in my db. For instance, suppose the field's name is 'color', and possible values are 0, 1, 2, 3, etc.

If the field has a value of 0, that means it is blue.
1=yellow
2=green
3=black
etc

From a development standpoint, is it better to have a method that converts them
ie

Code: Select all

if($color == 1)
   $returnval = "yellow";
if($color == 2)
   $returnval = "green";
return $returnval;
Or is it better to have these conversions in a database table?

I'm inclined to think it's better to have a table with 2 columns (code and color), then you can make a UI so users can add/modify/delete colors, but I'm wondering if there's an industry standard. Thanks in advance

Posted: Fri Dec 15, 2006 2:42 pm
by Begby
You want it in a separate table and do a join Anything that is data should go in the database for sure, you don't want that kind of stuff hard coded. This makes it so you can have an interface modify the colors as you stated, but also if you want to access the data from a different application down the road you don't have to recode all of that stuff. There are a lot of other reasons as well.

Posted: Fri Dec 15, 2006 2:48 pm
by Ollie Saunders
Or is it better to have these conversions in a database table?
Why are you using a code? If the code means something like a pantone number then you will probably want to create a separate table linking codes to name. If however the code is there just because you think it might be a good idea you are better off dropping it and just using strings everywhere.
but I'm wondering if there's an industry standard
There never could be for such a thing. Its a decision you have to make yourself. You have to assess whether it is necessary, or may be necessary in the future, to add, remove, update colours.

Here's my advice:

Code: Select all

if (codesHaveRealWorldMeaning or coloursWillChange) {
    use separate table
} else {
    use a single field as enum and work in strings
}