Storing Text Values Instead of ID's for the Sake of Users

[John Welch]

Hi all. I'm creating a FE/BE database that will be used by about 6 users. As
usual, I have several fields, such as "OrganizationTy peID" that will get
values (via combo boxes in forms) from separate lookup tables. I know the
'correct' way to do this, using a long integer as the field type. In this
case, however, I am considering doing it "incorrectl y", by actually storing
the text in my field rather than an ID, still using a lookup table, but
joining on the text and using cascading updates. I'm considering this
because the users are going to want to create their own queries and reports,
and it might be less confusing for them if they don't have to join to the
lookup tables all the time. It seems to me that the main disadvantages will
be the extra storage requirements (1 byte per character vs. 4 bytes per long
int) and the speed of data transfer. I think I can deal with integrity
issues with the cascading updates, but I haven't tried this before.

I'm asking to see if any experienced developers have done this same thing
with ok results, or if anyone has ideas against it that I haven't thought
of.
(I'm a pretty experienced Access developer)

thanks
-John

[Larry Linson]

"John Welch" <john(remove)we lch@cal(remove) central.com> wrote

I'm thinking of having the field actually store
text and having the text be the foreign key to
the lookup table which has text as it's
primary key.

Sorry for my misunderstandin g.

You can certainly use a text Field as the Primary Key of the related table,
and if you are careful in constructing it, it can be an abbreviation or
mnemonic that is obvious to the mere human user.

The only drawback to using a text Field for a Primary Key is that
Access/Jet/yourServer won't automatically generate it for you. You may need
more than a single Text field to uniquely identify a record, and, if so, you
might still have to create a "surrogate key" (that's the only purpose for an
Autonumber key, AFAICT).

Note to those who take strong positions on "natural" vs. "surrogate" keys.
Please argue that elsewhere, without expecting me to join in the argument.
Thanks.

Larry Linson
Microsoft Access MVP

[Larry Linson]

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .

Nope. I just update
eg Before Income Tax Season I run:
UPDATE Transactions SET Account = 'Charity Donation' WHERE Account =
'Beer'

Lyle, you have a hilarious sense of humor. If I am ever in your environs,
I'd like to make a "charity donation" on your behalf at the dispensary of
beverages of your choice.

[David W. Fenton]

"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in
news:dv******** *@enews2.newsgu y.com:

Thanks Larry. I can't tell if we're talking about the same thing,
though. I'm _not_ thinking of using "lookup fields" in the sense
of using Access' confusing built in thing where the field stores a
number but displays text. I'm thinking of having the field
actually store text and having the text be the foreign key to the
lookup table which has text as it's primary key.

For example:
tblOrganization s:
OrgID: autonum
OrgName: text
OrgType (fk): text (foundation, household, corporation, etc.)

tblOrgTypes:
OrgType (pk): text (foundation, household, corporation, etc.)

join with cascading updates

I don't see any problem with using real text for these kinds of
codes.

Note that it's really no different from using a table to store the
values for a validation rule, rather than being a relationship that
increases normalization. In this case, there is no benefit in
non-repetition from having the relationship because the entirety of
the related information is repeated. However, this is because it's a
single field that defines the uniqueness of the foreign key record.

This would also be the case of using multi-column natural keys. A
lookup table might have 3 columns, all of which were part of your
PK, and when stored as a foreign key, all three would be repeated.
This is a flaw in the argument for natural keys, in my opinion
(among many others), but for a single-column key, I see no real
problem.

I've done what you're contemplating many times and it works just
fine.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

"Larry Linson" <bo*****@localh ost.not> wrote in
news:p%ZSf.4205 $TK2.48@trnddc0 7:

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .

Nope. I just update
eg Before Income Tax Season I run:
UPDATE Transactions SET Account = 'Charity Donation' WHERE Account =
'Beer'

Lyle, you have a hilarious sense of humor. If I am ever in your
environs, I'd like to make a "charity donation" on your behalf at the
dispensary of beverages of your choice.

Your money won't be any good up here, Larry. (I hope this isn't a uniquely
Canadian expression and that you know what it means.)

--
Lyle Fairfield

[John Welch]

Thanks to everyone for the thoughtful and helpful comments. (and for the
humor too, Lyle: I got a great laugh out of that one.)
I'm going to get rid of the autonumbers in my one-field lookup tables.
-John