Design problem: Codes

[klintan]

I have a database design problem that I guess is very common, how to restrict code values in a database?

E.g. in a database you could have CustomerTypeCode, OrderTypeCode, ProductTypeCode, OrderLineCode, CustomerCategoryCode etc.

I can come up with at least three different designs that restricts these values in a database, but I would like to discuss pro's and con's of each of them, and maybe learn some other approaches as well.

Check constraints can be used
A code table per "type" can be used (e.g. CustomerTypes, OrderTypes)
A single table with all codes can be used, but in this case a CodeCategory is also needed to distinguish between different type of codes.
Currently, I use #2 if I have additional data associated to the "type" (for instance, I have a region table with region code, name, and some other data), and #3 for other codes.

Oh, and by the way I am using SQL Server and Access (both databases are supported by my applications).

[erickwidya]

..how to restrict code values in a database? can u explain a bit more about what u're doing with some example of "type" u mention about..

Currently, I use #2 if I have additional data associated to the "type" (for instance, I have a region table with region code, name, and some other data), and #3 for other codes. plz explain this too..

regards

[klintan]

can u explain a bit more about what u're doing with some example of "type" u mention about..

plz explain this too..

regards

My app is actually a forestry app with forest types (plants, trees, other) ground vegitation types (berries, grass, lichen etc.), tree types (pine, spruce etc.) , soil types (moist, dry, wet etc.), geographical regions (counties in Sweden) , climate types (continental, maritime etc.)

I have a general code table which I use for most codes, it has three columns, Code, CodeCategory, and Description,for instance:
1, 1, Plants
2, 1, Trees
1, 2, Berries
2, 2, Grass

CodeCategory is in another table with columns CodeCategory and Description, for instance:

1, Forest type
2, Ground veg. type

For some codes, like Region, I have a separate table with RegionId, Name, PartOfSweden (North, South, Middle), and some other columns.

[erickwidya]

I have a general code table which I use for most codes, it has three columns, Code, CodeCategory, and Description,for instance:
1, 1, Plants
2, 1, Trees
1, 2, Berries
2, 2, Grass

CodeCategory is in another table with columns CodeCategory and Description, for instance:

1, Forest type
2, Ground veg. type what about using one table, something like this
Code(PK), Description, RelatedTo(FK to Code)
1, ForestType, 0
2, Ground veg. type, 0
...
9, Trees, 1
10, Berries, 2
...
20, Pine, 9
21, Spruce, 10
0 : means there's no related

EDIT the rest looks fine
regards