Table design questions regarding storing numbers versus words

[Cheese_whiz]

My main table has several fields that are inputted via combo boxes in a
form, with the combo boxes bound to small tables with maybe only a field or
two....basically tables set up to provide the choices for the combo boxes AND
make it easier for users to add choices to the combo boxes.

My question concerns both the combo box tables and, ultimately the main
table itself:

Should I be designing my main table, and the combo boxes, so that in the
main table the choices made using the combo boxes are represented as numbers
instead of the actual words put in the main table? (<----this is my main
question)

I started out just using one column tables for the combo boxes. That
results in the actual words going into the main table which would seem to, at
least potentially, slow down the processes when this application gets split
and the back end gets put on a common network drive and the front ends are
installed on individual workstations. Then I added a couple more but this
time used two column tables for the combo boxes and while displaying the word
in the combo box, I was passing the number to the table.

My other concern is that if I change things now, then the reports I've
already set up based on a query based on the main table (mostly) will end up
showing the numbers instead of the words which won't be good. Is "fixing"
that just a matter of adding the "combo box" tables to the query and joining
them to the main table and maybe specifying that the combo box table's word
column should be displayed in place of the numerica value from the combo box
table stored in the main table?

Does any of that make sense?

TIA,
CW

 

[mscertified]

Yes you should have an autonum id as the primary key in your reference tables
and this key should be stored in your table and not the full text. You then
should go into 'table relationships' and define this relationship to enforce
referential integrity.

As far as the reports go you can fix them easily. You could make the report
combo bvoxes too. There are better ways to do it (changing the query) but
that will probably be easiest.

Dorian

 

[Cheese_whiz]

Thanks for the reply. Sort of what I was thinking.
CW