468,505 Members | 1,803 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,505 developers. It's quick & easy.

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

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 "OrganizationTypeID" 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 "incorrectly", 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
Mar 17 '06 #1
14 1519
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)welch@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 "OrganizationTypeID" 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 "incorrectly", 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

Mar 18 '06 #2
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:
tblOrganizations:
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*****@localhost.not> wrote in message
news:j4JSf.2986$TK2.1012@trnddc07...
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)welch@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 "OrganizationTypeID" 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 "incorrectly", 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


Mar 18 '06 #3
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.

Mar 18 '06 #4

"John Welch" <john(remove)welch@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 "OrganizationTypeID" 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 "incorrectly", 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.PrdColour = tblColour.ColID
WHERE tblColour.ColName="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???"

Mar 18 '06 #5

"John Welch" <john(remove)welch@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 "OrganizationTypeID" 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 "incorrectly", 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.PrdColour = tblColour.ColID
WHERE tblColour.ColName="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???"
Mar 18 '06 #6
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.googlegr oups.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.

Mar 18 '06 #7

"John Welch" <john(remove)welch@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 "OrganizationTypeID" 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 "incorrectly", 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.PrdColour = tblColour.ColID
WHERE tblColour.ColName="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???"
Mar 18 '06 #8
Nope. I just update
eg Before Income Tax Season I run:
UPDATE Transactions SET Account = 'Charity Donation' WHERE Account =
'Beer'

Mar 18 '06 #9
On Fri, 17 Mar 2006 15:10:38 -0800, "John Welch" <john(remove)welch@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 "OrganizationTypeID" 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 "incorrectly", 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!
Mar 18 '06 #10
"John Welch" <john(remove)welch@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 misunderstanding.

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
Mar 18 '06 #11

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.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.
Mar 18 '06 #12
"John Welch" <john(remove)welch@cal(remove)central.com> wrote in
news:dv*********@enews2.newsguy.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:
tblOrganizations:
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/
Mar 18 '06 #13
"Larry Linson" <bo*****@localhost.not> wrote in
news:p%ZSf.4205$TK2.48@trnddc07:

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.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
Mar 18 '06 #14
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
Mar 19 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mark | last post: by
3 posts views Thread by Adrian Parker | last post: by
23 posts views Thread by shank | last post: by
2 posts views Thread by Rick | last post: by
3 posts views Thread by gieforce | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.