Design ? re: code tables used to fill in fields in tables

[ps40life]

From what I have learned about db design, you always create a primary key and if there is none, you at least put an autonumber field. So, let's say I have a table I am just using to help the data entry process. It's basically used to populate a combo box so entries are consistent.

Example: Table: Program Change Codes
Field Name: Program ID (Autonumber)
Field Name: Program Change Description
Desc. entries like: Released from testing due to illness; Held back because for therapeutic counseling., etc...

When you use the autonumber field, then you choose 1 for the released option and 2 for the held back option.

Here is my quandary: First, a user has to know the autonumber to search in a query rather than using the actual "reason" for the program change. This seems confusing. If they are filling in a parameter query, is it best to just create a list box they can pick options from?
Second, what if they want to enter something not on the list, something out of the ordinary that will possibly never be used again and it is not necessary to add to the code table. It won't let you just enter text if it's an autonumber field.

I have always done these using the autonumber, code desc. field structure, but it seems to be causing issues when you are dealing with new users who may not understand the underlying structure. If the field shows the actual description when they fill in from the combo box, how are they supposed to know the autonumber field?

I have started just creating the table with one field-the actual code as the primary key, but when you have long "codes" (like the examples above), this doesn't seem right. HELP!

Thanks for any clarity that can be offered!!

 

[skiflyer]

As I see it you have two options...

The first is in the spirit of "coding" as in ICD codes and such. Publish the "autonumbers" and let them enter those, keep one number reserved for other and let them enter plain text.

The better in my mind would be to populate the drop down box with all the ones they can choose, that's the whole point of using a table like you have above, to make sure they only choose from an approved list. Then, if they don't find what they want, let them enter something new, and enter to your codes table, don't worry if it's never used again, that's no difference than saving that text straight someplace, and now you can run queries which tell you all sorts of information about how and when different reasons are used to justify different changes.

The short of what I said (I tend to ramble lately), is auto-populate a drop down box with the information, hide the auto-number from them, they don't really need to know anything about it.

-Rob

 

[ps40life]

OK. I agree with using the code table WITH the autonumber. It's great when entering in the table and you hide the autonumber so they only see the text of the code (not the auto#).
Going back to my question though, what about when you are working with queries? How do keep track of the autonumbers, because that is what is truly entered in the field. They can't search by the code-they have to use the autonumber, and they have to know that in the first place. This may sound stupid and against the whole idea of having a database-but the only thing I can think is to print the table and stick it by your computer to refer to the number of the code!

 

[lespaul]

One option for the query would be to use the LIKE statement. That way you can dynamically build your query to search the description field:

SELECT * FROM TABLE WHERE DESCRIPTION LIKE '%Released%'

That will return all the rows where Released is in the description field.

Other options include letting the users select the description from the table and then in the code get the number to search for.

You don't mention what database you are using, but the LIKE delimiter (%) is a (*) in Access (I THINK!!).

HTH

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[skiflyer]

My apologies for the delay... it's all about the join... so say the code table has this entry

Table:CODES
pkCode  description
234     Too far gone

and the other table is something like this (my example obviously simplified)

Table:USERS
name    fkCode date
Rob      234   5/2/2003
Jim      567   5/1/2003
Leslie   234   4/2/2002

SELECT users.name
FROM users, codes
WHERE codes.pkCODE=users.fkCODE AND codes.desc = 'Too far gone'

will return Rob and Leslie

Then depending on your database and such, you'll be able to mix in subqueries and all sorts of things... worse case scenario you'll need to make intermediate tables, but there will always be a query you can right.


-Rob