code tables 1

[rajkum]

Hi,
Is it good to have many code tables in a database?

Background:
I have a form which has many dropdowns.
I have created a table each for each dropdowns on the web form.
(It is on the form of item_cd and item_nm)

I have a main table where I insert all the codes and some extra info which is in the form of text.

Thanks,
Raj.

 

[MakeItSo]

Hi Raj.

If you want to change/add any values to your choice, you would have to update all your tables - not so good.
Better is to use a small query as row source, like e.g.

SELECT DISTINCT item_cd FROM your table

Do that for the other values as well and you can store all values in one table or even pull them out of existing main records.

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[fluteplr]

My preference is to use one table for all my basic dropdowns.

It has the following fields

code,
type,
description,
inactive,
displayorder,
system

Code is obvious, usually a char(10)

Type is used to segregate codes for various uses.


Description is obvious

inactive is a tinyint 0 is active, 1 means inactive. I have code in all my dropdowns that allow the inactive codes to show (for old data) but do not allow that value to be assigned anymore.

Displayorder tinyint, use when I want to display the codes in the dropdown is some nonobvious order.

system tinyint a value of 1 indicates that this is a code value that means something to the system (there is code dependant on it) as apposed to just a description of an item. An example would be Contact type which might have values of email, and phone. These would be marked as system since I have code in my application that specificially looks at this value to see what to do. A non system code whould be something like color. I might need to know what color something is but there is not specific code to handle blue differently than red.

Primary key for this table would be code, type.

I find that this structure handles about 90 percent of the code dropdowns I need. Those that require extra fields get their own tables.