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]

My experience is that lookup fields are ONLY useful for end-users displaying
data in datasheet view. In any other circumstance, they are more trouble
than they ever were worth to that novice/casual end user. IMNSHO, you will
be far better off to replace the Lookup Fields with a field containing the
Foreign Key and give the users some free training on Queries and Joins. Even
if you donate normally-paid consulting time, it'll pay you back in the long
run, when you aren't regularly getting phone calls from confused users.

That said, it is possible to redefine the Lookup Fields so that they appear
to do what you describe. But beware the day that users start to create
Queries on the Table -- that will be a good time to have folded your tent
and stolen away into the night.<GRIN>

Larry Linson
Microsoft Access MVP

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

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

[John Welch]

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
-John
"Larry Linson" <bo*****@localh ost.not> wrote in message
news:j4JSf.2986 $TK2.1012@trndd c07...

My experience is that lookup fields are ONLY useful for end-users
displaying data in datasheet view. In any other circumstance, they are
more trouble than they ever were worth to that novice/casual end user.
IMNSHO, you will be far better off to replace the Lookup Fields with a
field containing the Foreign Key and give the users some free training on
Queries and Joins. Even if you donate normally-paid consulting time, it'll
pay you back in the long run, when you aren't regularly getting phone
calls from confused users.

That said, it is possible to redefine the Lookup Fields so that they
appear to do what you describe. But beware the day that users start to
create Queries on the Table -- that will be a good time to have folded
your tent and stolen away into the night.<GRIN>

Larry Linson
Microsoft Access MVP

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

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

[Lyle Fairfield]

Yep! Example ... the account for a transaction. There are only a dozen
accounts or so, and thousands of transactions. But the account name is
stored in the transaction table, not the ID of some account from an
Accounts table. The Transactions form still has a pulldown for
accounts, it just references the Transactions table itself, "SELECT
DISTINCT Account From Transactions ORDER BY Account". Actually I do
something similar for details except its WHERE COUNT whatever over the
last three months > 2.
I'd like to tell you of all the problems I have had with this over the
past ten years or so because I'm a strong relational db kinda guy. But
I can't because I haven't had any problems. No. it's not slow. No it's
not huge. No, I haven't gone blind.
BTW what do zip programs zip the best? Text? ... When I zip up this
compacted and unencrypted db it's the size of a small fly.
The only minor problem is that we have to requery the pulldown after we
add a new account. This happens every seven years or so, whether we
like it or not, so we have to deal with this efficiently. And I have a
requery (everything) button on the pop-up menu for the form coz I want
to view some totals. So it happens as part of that, pretty seamlessly.

There are exceptions to many rules. I have an application with a
calculation that takes about 30 minutes of churning (today ... ten
years ago it was an over-nighter). It has to be done once a year and
then it's etched in stone by company policy. Its results may not be
changed. Do you think I follow the dictum of "Don't store calculated
results?" No I run it once and save the results to a table, and a
gazillion departments use the table for reports. Do we have a special
menu item to recalculate? Yes! Have we ever used it? Yes, but I can't
remember if it was in 1994 or 1995. Seems a piece of land we thought
was in the region was actually in another region so we got special
dispensation to do the thing again.

[Anthony England]

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

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

Yes, I do this sometimes in a couple of situations. Firstly, when the
lookup table is small and simple, e.g.
tblSize="Small" , "Medium", "Large"
tblColour="Red" , "White", "Blue"

I mean would I really create an extra table and write:

SELECT PrdID, PrdName, ColName
FROM tblProduct INNER JOIN tblColour
ON tblProduct.PrdC olour = tblColour.ColID
WHERE tblColour.ColNa me="red"

where I could write:

SELECT PrdID, PrdName, PrdColour
FROM tblProduct WHERE PrdColour="red"
The other time I do this is a bit nerdier, and is similar to Lyle's
comments. I sometimes have large transaction tables which relate to
products. Instead of having a simple ProductID in the table, I have a text
field such as "RM042" which tells me the product is "red", "medium",
region=0 and size=42. This allows me to query the table directly asking
questions like "how many large, blue shirts have I sold recently?" by
searching for "LB???"

[Anthony England]

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

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

Yes, I do this sometimes in a couple of situations. Firstly, when the
lookup table is small and simple, e.g.
tblSize="Small" , "Medium", "Large"
tblColour="Red" , "White", "Blue"

I mean would I really create an extra table and write:

SELECT PrdID, PrdName, ColName
FROM tblProduct INNER JOIN tblColour
ON tblProduct.PrdC olour = tblColour.ColID
WHERE tblColour.ColNa me="red"

where I could write:

SELECT PrdID, PrdName, PrdColour
FROM tblProduct WHERE PrdColour="red"
The other time I do this is a bit nerdier, and is similar to Lyle's
comments. I sometimes have large transaction tables which relate to
products. Instead of having a simple ProductID in the table, I have a text
field such as "RM042" which tells me the product is "red", "medium",
region=0 and size=42. This allows me to query the table directly asking
questions like "how many large, blue shirts have I sold recently?" by
searching for "LB???"

[John Welch]

Thanks Lyle. That helps.
Have you ever used a lookup table to store the allowed text values, with
cascading updates in case they ever want to change a spelling or something?
-John
"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .

Yep! Example ... the account for a transaction. There are only a dozen
accounts or so, and thousands of transactions. But the account name is
stored in the transaction table, not the ID of some account from an
Accounts table. The Transactions form still has a pulldown for
accounts, it just references the Transactions table itself, "SELECT
DISTINCT Account From Transactions ORDER BY Account". Actually I do
something similar for details except its WHERE COUNT whatever over the
last three months > 2.
I'd like to tell you of all the problems I have had with this over the
past ten years or so because I'm a strong relational db kinda guy. But
I can't because I haven't had any problems. No. it's not slow. No it's
not huge. No, I haven't gone blind.
BTW what do zip programs zip the best? Text? ... When I zip up this
compacted and unencrypted db it's the size of a small fly.
The only minor problem is that we have to requery the pulldown after we
add a new account. This happens every seven years or so, whether we
like it or not, so we have to deal with this efficiently. And I have a
requery (everything) button on the pop-up menu for the form coz I want
to view some totals. So it happens as part of that, pretty seamlessly.

There are exceptions to many rules. I have an application with a
calculation that takes about 30 minutes of churning (today ... ten
years ago it was an over-nighter). It has to be done once a year and
then it's etched in stone by company policy. Its results may not be
changed. Do you think I follow the dictum of "Don't store calculated
results?" No I run it once and save the results to a table, and a
gazillion departments use the table for reports. Do we have a special
menu item to recalculate? Yes! Have we ever used it? Yes, but I can't
remember if it was in 1994 or 1995. Seems a piece of land we thought
was in the region was actually in another region so we got special
dispensation to do the thing again.

[Anthony England]

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

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

<re-post as still not appeared on my news server>

Yes, I do this sometimes in a couple of situations. Firstly, when the
lookup table is small and simple, e.g.
tblSize="Small" , "Medium", "Large"
tblColour="Red" , "White", "Blue"

I mean would I really create an extra table and write:

SELECT PrdID, PrdName, ColName
FROM tblProduct INNER JOIN tblColour
ON tblProduct.PrdC olour = tblColour.ColID
WHERE tblColour.ColNa me="red"

where I could write:

SELECT PrdID, PrdName, PrdColour
FROM tblProduct WHERE PrdColour="red"
The other time I do this is a bit nerdier, and is similar to Lyle's
comments. I sometimes have large transaction tables which relate to
products. Instead of having a simple ProductID in the table, I have a text
field such as "RM042" which tells me the product is "red", "medium",
region=0 and size=42. This allows me to query the table directly asking
questions like "how many large, blue shirts have I sold recently?" by
searching for "LB???"

[Lyle Fairfield]

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

[polite person]

On Fri, 17 Mar 2006 15:10:38 -0800, "John Welch" <john(remove)we lch@cal(remove) central.com> wrote:

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

This is reminisent of the debate about natural versus artificial keys, where minds never meet.
I'd say go ahead as Lyle suggests.
Another advantage is that if your database gets screwed up you stand much more chance of recovering
the data, though this advantage is not nowadays very often needed.
Efficiency phooey, ten or more years ago people said that if you need efficiency don't use a
relational db. Maybe they are still right!