Just been having a tidy up on my PC and dusted off this old link I
had:
http://www.mvps.org/access/lookupfields.htm
I use lookups a lot! They are godsend to me. A typical orders table of
mine will have a (long) SalesRepID field that looks up the
tblSalesReps table containing SalesRepID (the lookup field),
RepFirstName, RepLastName. What is the alternative, "better", approach
to this?
Paul
No: Lookup fields are not evil. Any non-trivial relational database will
include several lookup tables, with the related foreign keys in your other
tables.
I think the author of that article is arguing against using the Lookup
Wizard in table design to create lookup fields. He believes that using
combos in the table -- particularly combos where the bound field is
zero-width -- creates more confusion that it does benefit. People don't
understand what's stored there, and then get stuck with trying to provide
lookup values that suddenly don't even match the correct data type, because
what you see is not what you get.
That, plus the way the lookup wizard messes up the indexes and relations in
the database. So the criticism questions whether the the lookup wizard
creates more problems that it solves. It is not advising against the user of
lookup fields in your tables.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Paul H"
news:6825d07f-dc4a-4726-a6e2-f25495386665@m44g2000hsc.google groups.com...
> Just been having a tidy up on my PC and dusted off this old link I
> had:
>
> http://www.mvps.org/access/lookupfields.htm
>
> I use lookups a lot! They are godsend to me. A typical orders table of
> mine will have a (long) SalesRepID field that looks up the
> tblSalesReps table containing SalesRepID (the lookup field),
> RepFirstName, RepLastName. What is the alternative, "better", approach
> to this?
>
> Paul
On Apr 8, 4:56=A0am, Paul H
> Just been having a tidy up on my PC and dusted off this old link I
> had:
>
> http://www.mvps.org/access/lookupfields.htm
>
> I use lookups a lot! They are godsend to me. A typical orders table of
> mine will have a (long) SalesRepID field that looks up the
> tblSalesReps table containing SalesRepID (the lookup field),
> RepFirstName, RepLastName. What is the alternative, "better", approach
> to this?
>
> Paul
Joins
Combo Boxes
List Boxes
Sub-forms
A facility with SQL
Compartmentalization
I feel no loss in having never created or used a Lookup field of my
own.
On Apr 8, 5:09=A0am, "Allen Browne"
> No: Lookup fields are not evil. Any non-trivial relational database will
> include several lookup tables, with the related foreign keys in your other=
> tables.
>
> I think the author of that article is arguing against using the Lookup
> Wizard in table design to create lookup fields. He believes that using
> combos in the table -- particularly combos where the bound field is
> zero-width -- creates more confusion that it does benefit. People don't
> understand what's stored there, and then get stuck with trying to provide
> lookup values that suddenly don't even match the correct data type, becaus=
e
> what you see is not what you get.
>
> That, plus the way the lookup wizard messes up the indexes and relations i=
n
> the database. So the criticism questions whether the the lookup wizard
> creates more problems that it solves. It is not advising against the user =
of
> lookup fields in your tables.
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
I think Arvin and Joan should say what they meant or mean.
I strongly disagree with you and especially with,
> Any non-trivial relational database will
> include several lookup tables, with the related foreign keys in your other=
> tables.
I would not recommend to my clients the purchase of such a database.
Regardless of whether the article advises against the use of Lookup
Tables (and I believe that it does), I do.
On Apr 8, 5:09=A0am, "Allen Browne"
> No: Lookup fields are not evil. Any non-trivial relational database will
> include several lookup tables, with the related foreign keys in your other=
> tables.
>
> I think the author of that article is arguing against using the Lookup
> Wizard in table design to create lookup fields. He believes that using
> combos in the table -- particularly combos where the bound field is
> zero-width -- creates more confusion that it does benefit. People don't
> understand what's stored there, and then get stuck with trying to provide
> lookup values that suddenly don't even match the correct data type, becaus=
e
> what you see is not what you get.
>
> That, plus the way the lookup wizard messes up the indexes and relations i=
n
> the database. So the criticism questions whether the the lookup wizard
> creates more problems that it solves. It is not advising against the user =
of
> lookup fields in your tables.
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Paul H"
>
> news:6825d07f-dc4a-4726-a6e2-f25495386665@m44g2000hsc.google groups.com...
>
> > Just been having a tidy up on my PC and dusted off this old link I
> > had:
>
> >http://www.mvps.org/access/lookupfields.htm
>
> > I use lookups a lot! They are godsend to me. A typical orders table of
> > mine will have a (long) SalesRepID field that looks up the
> > tblSalesReps table containing SalesRepID (the lookup field),
> > RepFirstName, RepLastName. What is the alternative, "better", approach
> > to this?
>
> > Paul
=46rom http://www.mvps.org/access/tencommandments.htm
.. thou shalt abhor the use of "Lookup Fields" which art the creation
of the Evil One.
"lyle"
news:acd520f3-8e62-415b-b387-5d2b7adf2c5f@m1g2000pre.googleg roups.com...
> I strongly disagree with you and especially with,
>> Any non-trivial relational database will
>> include several lookup tables, with the related foreign keys in your
>> other
>> tables.
>I would not recommend to my clients the purchase of such a database.
I'm not sure I understand, Lyle.
In the Northwind 2003 sample database, the Product table contains a
CategoryID field, indicating what category each product belongs to. This
field is a foreign key to Categories.CategoryID.
The way I understand the term "lookup", the Categories table is a lookup
table, and Product.CategoryID is a lookup field. Are you arguing that this
is a bad design? Or are you saying that this is not a lookup field?
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Allen Browne"
news:47fb45de$0$13252$5a62ac22@per-qv1-newsreader-01.iinet.n et.au:
> "lyle"
> news:acd520f3-8e62-415b-b387-5d2b7adf2c5f@m1g2000pre.googleg roups.com..
> .
>> I strongly disagree with you and especially with,
>>> Any non-trivial relational database will
>>> include several lookup tables, with the related foreign keys in your
>>> other
>>> tables.
>>I would not recommend to my clients the purchase of such a database.
>
> I'm not sure I understand, Lyle.
>
> In the Northwind 2003 sample database, the Product table contains a
> CategoryID field, indicating what category each product belongs to.
> This field is a foreign key to Categories.CategoryID.
>
> The way I understand the term "lookup", the Categories table is a
> lookup table, and Product.CategoryID is a lookup field. Are you
> arguing that this is a bad design? Or are you saying that this is not
> a lookup field?
Very bad design.
On Tue, 08 Apr 2008 10:34:43 GMT, lyle fairfield
wrote:
Elaborate. Not tersly.
-Tom.
>"Allen Browne"
>news:47fb45de$0$13252$5a62ac22@per-qv1-newsreader-01.iinet. net.au:
>
>> "lyle"
>> news:acd520f3-8e62-415b-b387-5d2b7adf2c5f@m1g2000pre.googleg roups.com..
>> .
>>> I strongly disagree with you and especially with,
>>>> Any non-trivial relational database will
>>>> include several lookup tables, with the related foreign keys in your
>>>> other
>>>> tables.
>>>I would not recommend to my clients the purchase of such a database.
>>
>> I'm not sure I understand, Lyle.
>>
>> In the Northwind 2003 sample database, the Product table contains a
>> CategoryID field, indicating what category each product belongs to.
>> This field is a foreign key to Categories.CategoryID.
>>
>> The way I understand the term "lookup", the Categories table is a
>> lookup table, and Product.CategoryID is a lookup field. Are you
>> arguing that this is a bad design? Or are you saying that this is not
>> a lookup field?
>
>Very bad design.
On Apr 8, 10:08=A0am, Tom van Stiphout
> On Tue, 08 Apr 2008 10:34:43 GMT, lyle fairfield
> wrote:
>
> Elaborate. Not tersly.
>
> -Tom.
>
> >"Allen Browne"
> >news:47fb45de$0$13252$5a62ac22@per-qv1-newsreader-01.iinet. net.au:
>
> >> "lyle"
> >>news:acd520f3-8e62-415b-b387-5d2b7adf2c5f@m1g2000pre.googl egroups.com..
> >> .
> >>> I strongly disagree with you and especially with,
> >>>> Any non-trivial relational database will
> >>>> include several lookup tables, with the related foreign keys in your
> >>>> other
> >>>> tables.
> >>>I would not recommend to my clients the purchase of such a database.
>
> >> I'm not sure I understand, Lyle.
>
> >> In the Northwind 2003 sample database, the Product table contains a
> >> CategoryID field, indicating what category each product belongs to.
> >> This field is a foreign key to Categories.CategoryID.
>
> >> The way I understand the term "lookup", the Categories table is a
> >> lookup table, and Product.CategoryID is a lookup field. Are you
> >> arguing that this is a bad design? Or are you saying that this is not
> >> a lookup field?
>
> >Very bad design.
It seems unnecessary to repeat the evils of lookup fields from my own
point of view. They are very well-summed up below (taken form the uri
given by the original poster).
The first Evil springs out when opens the Products Table. One sees, in
the Category Field, "Beverages"; what is actually in the Category
Field is 1. This is misinformation.
* ---------
The Evils of Lookup Fields in Tables
Contributors
Arvin Meyer
Joan Wild
A Lookup field in a table displays the looked-up value. For instance,
if a user opens a table datasheet and sees a column of company names,
what is in the table is, in fact, a numeric CompanyID, and the table
is linked with a select statement to the company table by that ID.
Any query that uses that lookup field to sort by that company name
won't work. Nor will a query that uses a company name in that field as
a criteria. If a user creates a combobox to select the company using a
value list, the data in the table can be over-written.
Another relationship is created which then creates another set of
indexes when a Lookup field is created, thus bloating the database
unnecessarily.
If a combobox based on the lookup is used in a form, and a filter is
applied, the persistent filter effect of Access often saves the filter
and the next time the form is opened, there will be a prompt for the
value (which cannot be provided, thus creating an error).
Reports based on the lookup field need a combobox to display the data,
causing them to run more slowly. The underlying recordsource can also
be modified to include the table, however the index, (unless it was
set up within a proper relationship) may not be optimized.
Lookup fields mask what is really happening, and hide good relational
methodology from the user.
The database cannot be properly upsized to, or queried by, another
engine (without removing all the lookup fields) because no other
engines use or understand them.
If security is implemented, permissions to tables is usually denied,
and RWOP queries are used for data access. There will often be errors
that there are no permissions on a specific table that isn't even
being used in a query (because the lookup field is). If the queries
are nested or complex, it can take some time to track down the lookup
that's causing the error (that is, if it occurs to you).
* ------------
BTW, this page, The Evils of Lookup Fields in Tables, is pointed to
from (on the same site http://www.mvps.org/access/) The Ten
Commandments of Access, Commandment #2, "Thou shalt never allow thy
users to see or edit tables directly, but only through forms and thou
shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One."
I think Allen's suggestion that "So the criticism questions whether
the the lookup wizard creates more problems that it solves. It is not
advising against the user of lookup fields in your tables." is not
supported.
"Allen Browne"
news:47fb45de$0$13252$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
> "lyle"
> =
news:acd520f3-8e62-415b-b387-5d2b7adf2c5f@m1g2000pre.googleg roups.com...
>> I strongly disagree with you and especially with,
>>> Any non-trivial relational database will
>>> include several lookup tables, with the related foreign keys in your =
>>> other
>>> tables.
>>I would not recommend to my clients the purchase of such a database.
>=20
> I'm not sure I understand, Lyle.
>=20
> In the Northwind 2003 sample database, the Product table contains a=20
> CategoryID field, indicating what category each product belongs to. =
This=20
> field is a foreign key to Categories.CategoryID.
>=20
> The way I understand the term "lookup", the Categories table is a =
lookup=20
> table, and Product.CategoryID is a lookup field. Are you arguing that =
this=20
> is a bad design? Or are you saying that this is not a lookup field?
>=20
> --=20
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
I am not sure I understand also...
Quote from your website: http://allenbrowne.com/ser-27.html :
Some developers hate the idea of a combo in a table anyway.=20
Particularly if the Bound Column is not the display value, it confuses =
people by masking what is really stored there, not to mention the issues =
with the wizard that creates this.
I agree with this, so I was thinking you are against these lookup-fields =
also.
So I agreed with your explanation here:
> In the Northwind 2003 sample database, the Product table contains a=20
> CategoryID field, indicating what category each product belongs to. =
This=20
> field is a foreign key to Categories.CategoryID.
However you are giving an example from Northwind 2003 that indeed uses =
these 'evil' ones...=20
==> The particular field CategoryID *shows* a value that is =
confusing people by masking ....
Same for SupplierID.=20
????
Arno R
On Apr 8, 3:56=A0am, Paul H
..
>
> I use lookups a lot! ... What is the alternative, "better", approach
> to this?
Paul, lookups hard coded into the table via the lookup tab in table
design are evil. However, I don't believe that they wanted to be
evil, they just had a difficult childhood.
A good developer maintains control and freedom by choosing when and
how to help the user connect the parent and child values (or we can
use the more 'evil' MS language in subforms and call them 'Master' and
'Child' relationship - there's a developer who might have had a bad
childhood as well)
I've had a whole bunch of clients who have learned the codes for
various business process and that's what they want to enter - so I
often give them fields for the codes, and next to a combo box for
those user who'd like to look up the value. C'est le meilleur des
mondes possibles!
Tim
"Paul H"
Lookup Fields are, indeed, wicked, evil, the very dark spirits of
computering incarnate... a punishment visited on the Great Access Unwashed
by a cabal of conspirators in Redmond WA because we were insufficiently
opposed to *pen S*urce.
They can be useful to the novice end-user who views data in Table Datasheet
view. They have been known to reduce the same novice end-user who tries to
use that Field in a Query to a shambling, glaze-eyed, thick-tongued Zombie
state -- they know they have been seeing "Doctor", "Lawyer", "Beggarman",
"Thief" in the Datasheet, but the Query returns "1", "2", "3", and "4".
Some rock-star Product Manager or Designer thought it would be helpful to
users to "automate" the concept of a Joined "Lookup Table", but it has
clearly been more trouble to users and to people answering questions in the
newsgroups than it could ever have been help to end-users in datasheet view.
A "Lookup Table" will have a Primary Key and other information (like a name
or description Field), referenced by a "Foreign Key" in another table.
Properly JOINed on the Primary and Foreign Key, the Query will show the
other information, but looking at the Table will show the Primary Key.
With the "Lookup Field" tab, this has been automated (without telling you,
of course), so that even in Datasheet View, you see the "other information",
but what's actually stored in your table is the Foreign Key. A Query does
not delve deep enough into the field properties to automatically compensate
for the design flaw, so shows you the Foreign Key, which prior to that time,
you had no idea existed.
Some of the discussion here hasn't been as clear as it might in making the
distinction between "Lookup Fields" and "Lookup Tables". But you have asked
the proper question in your subject line:
Ans. "Lookup Fields" are, in truth, "evil" because they obscure what is
actually in the Table; "Lookup Tables" are a proper approach because having
implemented them yourself, you know what is stored in each of the two Tables
involved and won't be "unpleasantly surprised".
Larry Linson
Microsoft Office Access MVP
On Tue, 8 Apr 2008 01:56:04 -0700 (PDT), Paul H
>Just been having a tidy up on my PC and dusted off this old link I
>had:
>
>http://www.mvps.org/access/lookupfields.htm
>
>I use lookups a lot! They are godsend to me. A typical orders table of
>mine will have a (long) SalesRepID field that looks up the
>tblSalesReps table containing SalesRepID (the lookup field),
>RepFirstName, RepLastName. What is the alternative, "better", approach
>to this?
>
>Paul
Well.. my 2 cents here..
I strongly believe that lookup fields are god send if you know how to
manage them.
I'm not an expert on Access but I have my share of experience and in
the past, I also discover what people are calling "evil". Basically,
access will store the "ID" or primary key of the record NOT what is
displayed on the actual lookup.
How I solved this? After years of learning, trial and errors. I have
every conceivable routine on my forms that make sure everything is ok
before saving the record. When I use lookup fields, I have vba events
that checks everything before saving.
Also, I'm well aware that when it comes to "queries" I have to make
sure that either I link the "data table" who have the description I
want or use the lookup function to display the description and NOT the
ID. Once I have the "Description" field, I sort, group, etc. with no
problem, everything works fine; for me at least.
Here's the basic Idea:
SELECT tblMyTable.MyTablePK, tblSalesReps.RepFirstName,
tblSalesReps.RepLastName, tblMyTable.MyFieldA, tblMyTable.MyFieldB,
tblMyTable.MyFieldC
FROM tblMyTable LEFT JOIN tblSalesReps ON tblMyTable.SalesRepID =
tblSalesReps.SalesRepID
ORDER BY tblMyTable.MyTablePK;
So, if you are aware of the ID thing, and you design with it in mind,
you should be ok.
Thanks.
Paul H
> Just been having a tidy up on my PC and dusted off this old link I
> had:
>
> http://www.mvps.org/access/lookupfields.htm
>
> I use lookups a lot! They are godsend to me. A typical orders table of
> mine will have a (long) SalesRepID field that looks up the
> tblSalesReps table containing SalesRepID (the lookup field),
> RepFirstName, RepLastName. What is the alternative, "better", approach
> to this?
>
> Paul
So after you got plenty of advice why lookup fields are - or are not -
evil, here is another thought that takes a different approach.
Say you have your lookup table (* denotes the primary key field)
*ID Fruit
-- -----
1 Apple
2 Orange
3 Pear
My question is: What is the ID used for? Why not just use
*Fruit
-----
Apple
Orange
Pear
Is it that perhaps, at a later time, you would like to change "Pear"
to "Banana" without changing the primary key, as often argued in this
context? (Are you really really sure you want to do this???)
Or is it that you fear to run low on memory or disk space or that you
will have an awful performance if you use the string variable as the
key?
Is it that you don't like the fact that all confusion with the
so-called "lookup-fields" is gone once you dispose of the mostly
unneccesary ID field?
Or are you of the persuasion that "every table needs an autonumber ID
field as primary key"?
Of course Paul, I don't mean "you" personally in the above questions,
this is more of a general "you" directed at any reader.
Anyway, very seriously: Whenever I feel myself wanting to create a
table with two fields, one an ID (Integer), and the second a "Content"
(String), I ask myself: What is the ID used for? Can't I just as well
use the "Content" field as the primary key? Of course the anwer is
(almost) every time very clear:
Abolish ID's!
Then you will not fall into the trap of the
Evil Lookup Fields
because, miraculously --- it has disappeared!
Greetings
Matthias Kläy
--
www.kcc.ch
On Tue, 8 Apr 2008 07:26:35 -0700 (PDT), lyle
I'm glad I asked.
You responded to:
The way I understand the term "lookup", the Categories table is a
lookup table, and Product.CategoryID is a lookup field. Are you
arguing that this is a bad design? Or are you saying that this is
not a lookup field?
by saying:
Very bad design.
I read that differently than you did. I read that a Northwind-like
design with two tables Products and Categories in 1:M via CategoryID
is what the user wanted to do. I think we both agree that's good.
I do agree with you to leave the dropdowns out of the table design,
because it can create confusion.
-Tom.
>On Apr 8, 10:08 am, Tom van Stiphout
>> On Tue, 08 Apr 2008 10:34:43 GMT, lyle fairfield
>> wrote:
>>
>> Elaborate. Not tersly.
>>
>> -Tom.
>>
>> >"Allen Browne"
>> >news:47fb45de$0$13252$5a62ac22@per-qv1-newsreader-01.iinet. net.au:
>>
>> >> "lyle"
>> >>news:acd520f3-8e62-415b-b387-5d2b7adf2c5f@m1g2000pre.googl egroups.com..
>> >> .
>> >>> I strongly disagree with you and especially with,
>> >>>> Any non-trivial relational database will
>> >>>> include several lookup tables, with the related foreign keys in your
>> >>>> other
>> >>>> tables.
>> >>>I would not recommend to my clients the purchase of such a database.
>>
>> >> I'm not sure I understand, Lyle.
>>
>> >> In the Northwind 2003 sample database, the Product table contains a
>> >> CategoryID field, indicating what category each product belongs to.
>> >> This field is a foreign key to Categories.CategoryID.
>>
>> >> The way I understand the term "lookup", the Categories table is a
>> >> lookup table, and Product.CategoryID is a lookup field. Are you
>> >> arguing that this is a bad design? Or are you saying that this is not
>> >> a lookup field?
>>
>> >Very bad design.
>
>It seems unnecessary to repeat the evils of lookup fields from my own
>point of view. They are very well-summed up below (taken form the uri
>given by the original poster).
>
>The first Evil springs out when opens the Products Table. One sees, in
>the Category Field, "Beverages"; what is actually in the Category
>Field is 1. This is misinformation.
>
>* ---------
>The Evils of Lookup Fields in Tables
>
>Contributors
>
>Arvin Meyer
>Joan Wild
>A Lookup field in a table displays the looked-up value. For instance,
>if a user opens a table datasheet and sees a column of company names,
>what is in the table is, in fact, a numeric CompanyID, and the table
>is linked with a select statement to the company table by that ID.
>
>Any query that uses that lookup field to sort by that company name
>won't work. Nor will a query that uses a company name in that field as
>a criteria. If a user creates a combobox to select the company using a
>value list, the data in the table can be over-written.
>
>Another relationship is created which then creates another set of
>indexes when a Lookup field is created, thus bloating the database
>unnecessarily.
>
>If a combobox based on the lookup is used in a form, and a filter is
>applied, the persistent filter effect of Access often saves the filter
>and the next time the form is opened, there will be a prompt for the
>value (which cannot be provided, thus creating an error).
>
>Reports based on the lookup field need a combobox to display the data,
>causing them to run more slowly. The underlying recordsource can also
>be modified to include the table, however the index, (unless it was
>set up within a proper relationship) may not be optimized.
>
>Lookup fields mask what is really happening, and hide good relational
>methodology from the user.
>
>The database cannot be properly upsized to, or queried by, another
>engine (without removing all the lookup fields) because no other
>engines use or understand them.
>
>If security is implemented, permissions to tables is usually denied,
>and RWOP queries are used for data access. There will often be errors
>that there are no permissions on a specific table that isn't even
>being used in a query (because the lookup field is). If the queries
>are nested or complex, it can take some time to track down the lookup
>that's causing the error (that is, if it occurs to you).
>* ------------
>
>BTW, this page, The Evils of Lookup Fields in Tables, is pointed to
>from (on the same site http://www.mvps.org/access/) The Ten
>Commandments of Access, Commandment #2, "Thou shalt never allow thy
>users to see or edit tables directly, but only through forms and thou
>shalt abhor the use of "Lookup Fields" which art the creation of the
>Evil One."
>I think Allen's suggestion that "So the criticism questions whether
>the the lookup wizard creates more problems that it solves. It is not
>advising against the user of lookup fields in your tables." is not
>supported.
Tom van Stiphout
news:ac5ov31ahv10uqd8l5tmstknrulv53qmlu@4ax.com:
> On Tue, 8 Apr 2008 07:26:35 -0700 (PDT), lyle
>
>
> I'm glad I asked.
>
> You responded to:
> The way I understand the term "lookup", the Categories table is a
> lookup table, and Product.CategoryID is a lookup field. Are you
> arguing that this is a bad design? Or are you saying that this is
> not a lookup field?
> by saying:
> Very bad design.
>
> I read that differently than you did. I read that a Northwind-like
> design with two tables Products and Categories in 1:M via CategoryID
> is what the user wanted to do. I think we both agree that's good.
>
> I do agree with you to leave the dropdowns out of the table design,
> because it can create confusion.
>
> -Tom.
I don't understand why a table would be designated as Lookup. What does
this mean? Does it mean the data are meaningless except in respect to the
field in another table for which they provide a "lookup"? Surely any table
can be used as the basis for a drop-down, and every table, whether used as
the basis for a drop-down or not, must contain some meaningful data or not
merit existence.
I have no lookup fields. I have no lookup tables. I do have tables from
which I choose values but this is not their main reason for existence.
"Lookup Fields" are an abomination and "Lookup Tables" don't exist beyond
legend.
On Wed, 09 Apr 2008 01:53:20 GMT, lyle fairfield
wrote:
Granted the term is not very useful. ANY table on the one-side of a
relation can be dubbed a Lookup table.
The term is usually reserved for tables with only two columns: ID and
Description, in which case it's common to say that one is "looking up"
the human-readable representation for an ID value. These tables are
often represented in the UI as dropdown lists.
-Tom.
>
>I don't understand why a table would be designated as Lookup. What does
>this mean? Does it mean the data are meaningless except in respect to the
>field in another table for which they provide a "lookup"? Surely any table
>can be used as the basis for a drop-down, and every table, whether used as
>the basis for a drop-down or not, must contain some meaningful data or not
>merit existence.
>I have no lookup fields. I have no lookup tables. I do have tables from
>which I choose values but this is not their main reason for existence.
>
>"Lookup Fields" are an abomination and "Lookup Tables" don't exist beyond
>legend.
"Allen Browne"
news:47fb364d$0$13300$5a62ac22@per-qv1-newsreader-01.iinet.n et.au:
> That, plus the way the lookup wizard messes up the indexes and
> relations in the database. So the criticism questions whether the
> the lookup wizard creates more problems that it solves. It is not
> advising against the user of lookup fields in your tables.
I have to disagree with your reading. You can create lookup fields
in a table without using the wizard (just as you can in a query),
and it's the characteristics of the lookup fields that are the
problem, i.e., hiding the real data with a combo box.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
lyle
news:acd520f3-8e62-415b-b387-5d2b7adf2c5f@m1g2000pre.googleg roups.com
:
> Regardless of whether the article advises against the use of
> Lookup Tables (and I believe that it does), I do.
Lookup tables or lookup fields defined in the table definitions?
Obviously, lookup tables are just fine.
Lookup fields defined in the table definition are a huge problem
that nobody should be using.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Allen Browne"
news:47fb45de$0$13252$5a62ac22@per-qv1-newsreader-01.iinet.n et.au:
> In the Northwind 2003 sample database, the Product table contains
> a CategoryID field, indicating what category each product belongs
> to. This field is a foreign key to Categories.CategoryID.
>
> The way I understand the term "lookup", the Categories table is a
> lookup table, and Product.CategoryID is a lookup field. Are you
> arguing that this is a bad design? Or are you saying that this is
> not a lookup field?
That's not a "lookup field," but a foreign key.
A "lookup field" is where you go into table design and replace the
display of the actual foreign key value with a dropdown list. This
is what I think you were talking about when you referred to the
lookup field wizard.
I think we can all agree that defining a dropdown display in the
table definition that hides the real data for a foreign key is a
massively bad idea. I've always referred to that as "lookup fields."
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
EI User
news:i0qnv3h44je8sou6s9ngosr80lh174453i@4ax.com:
> I strongly believe that lookup fields are god send if you know how
> to manage them.
Lookup fields are grand in a query or in a datasheet form or in any
kind of form at all.
They are an abomination if ever used in table design.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Matthias Klaey"
> So after you got plenty of advice why lookup fields
> are - or are not - evil, here is another thought that
> takes a different approach.
>
> Say you have your lookup table (* denotes the primary key field)
>
> *ID Fruit
> -- -----
> 1 Apple
> 2 Orange
> 3 Pear
>
> My question is: What is the ID used for? Why not just use
>
> *Fruit
> -----
> Apple
> Orange
> Pear
> Is it that perhaps, at a later time, you would like to change "Pear"
> to "Banana" without changing the primary key, as often argued in this
> context? (Are you really really sure you want to do this???)
It _does_ seem unlikely that a pear will morph into a banana, but it's not
at all unlikely that the palette/selection of colors for a product's finish
will change.
> Or is it that you fear to run low on memory or disk space or that you
> will have an awful performance if you use the string variable as the
> key?
Unquestionably, it you are talking about just fruit, there will not be a
significant saving in storage, until you deal with longer descriptive text,
and many, many records. With a million records, the long integer is 4
bytes; if the average length of description were 8 bytes, using the
link-to-the-ID would save 4MB... if you have several LookUps, you might save
4MB for each.
> Is it that you don't like the fact that all confusion
> with the so-called "lookup-fields" is gone once
> you dispose of the mostly unneccesary ID field?
I don't understand the (presumably rhetorical) question? Why would anyone
object to a reduction in confusion? Of course, with the foreign-to-primary
key joins gone, it would no longer be either a lookup field or a lookup
table. It might only be a validation table, but not "lookup".
> Or are you of the persuasion that "every table
> needs an autonumber ID field as primary key"?
Many server databases require a unique key if the data in a Table is to be
updated. Using MDB <-> ODBC <-> Microsoft SQL Server with an ADP front-end
in one enhancement project, we discovered that the original implementer, not
knowing (and perhaps afraid to expose his ignorance by asking) did not have
PKs put on SQL Server Tables, and, as a result, had had to use unbound forms
and fill them with VBA code. One of the enhancements was well-served by use
of a Bound Form with a Subform Control, so I had to request the DBA to put
Primary Keys on two Tables so I could bind the Form. It worked nicely, but
they didn't want to upgrade for anything but essential features, because
they were "implementing an Enterprise Requirements Planning package which
would replace that database". As far as I have heard, it's three years
later, the ERP project is still not fully impmemented, and the Access - MS
SQL server project is still running.
I've worked on other Access Clients to server backends where the DBA did put
a unique key (the Informix Server DB's equivalent of AutoNumber) in each
record. He thought, and rightly, that using a unique key as a surrogate key
was a convenience because the natural key in many cases was composed of
muliple fields.
> Anyway, very seriously: Whenever I feel myself
> wanting to create a table with two fields, one an
> ID (Integer), and the second a "Content"
> (String), I ask myself: What is the ID used for?
> Can't I just as well use the "Content" field as the
> primary key? Of course the anwer is very clear:
> Abolish ID's!
There's a subsantial body of Developers around who would argue that "it is
folly" to not consider each situation befoe making the decision.
> Then you will not fall into the trap of the
> Evil Lookup Fields
Larry Linson
Microsoft Office Access MVP
Hi Guys, just my two cents here....
I have created several databases in Access that do not use ID fields,
but rather use the information itself as the primary key. The reason
for approaching the dsign this way was one based simply on the needs
of the application / business case. Surrogates certainly have their
purpose, but are by no means necessary in my experience. You do
however have to consider the design of the database and application
before you embark on either path.
For the record I have not had any issues using multiple fields as
primary keys when not using surrogates, and they seem to work fine.
Would I do this everytime I build a db / app? Nope, only when it suits
the purpose at hand.
Did this get me around the problem of the 'lookup field'. As far as I
am concerned there is no problem with lookup fields. I just dont use
them. Period. They are a form control trying to be embedded into a
table. From my point of view this is not the job of a table, and so it
shouldnt be there. If you want users to enter data then bloodywell do
the work and give them a form to do it with. At least this way you can
do some more advanced validation and present the information /
controls in a more user freindly way. The drop down lookup field
should really have no place for an experienced programmer. Maybe for a
learner, but for someone who is supposed to know what they are doing
then understanding relations and normalisation isnt that hard, and for
that matter neither is basic form design. Even a basic form should
look better and exceed that capabilites of trying to directly enter
data into a table. As far as I am concerned they are just lazy.
---END RANT---
Sorry about that, got a bit more wound up than I thought :-)
Cheers
The Frog
"Larry Linson"
> "Matthias Klaey"
>
[...]
>
> I've worked on other Access Clients to server backends where the DBA did put
> a unique key (the Informix Server DB's equivalent of AutoNumber) in each
> record. He thought, and rightly, that using a unique key as a surrogate key
> was a convenience because the natural key in many cases was composed of
> muliple fields.
>
> > Anyway, very seriously: Whenever I feel myself
> > wanting to create a table with two fields, one an
> > ID (Integer), and the second a "Content"
> > (String), I ask myself: What is the ID used for?
> > Can't I just as well use the "Content" field as the
> > primary key? Of course the anwer is very clear:
>
> > Abolish ID's!
>
> There's a subsantial body of Developers around who would argue that "it is
> folly" to not consider each situation befoe making the decision.
>
> > Then you will not fall into the trap of the
> > Evil Lookup Fields
>
> Larry Linson
> Microsoft Office Access MVP
I agree with your differentiation of the issue. Of course I consider
every situation by itself with respect to performance and memory and
storage issues. You can see that in my original text where you have
deleted words without using an ellipsis. Your "cite" me as saying:
"Of course the anwer is very clear:"
But my original text is:
"Of course the anwer is (almost) every time very clear:"
My point is that you should use an ID - Content pair only after you
have *proofen* that using the Content Only would degrade the
application to a degree that is unacceptable for the client. And do
not forget in this cosideration that every time you use the ID -
Contents pair in a form or report you will have a table join that is
absent in the Contents Only scenario.
The very same applies to the "surrogate key" versus "composite natural
keys".
Greetings
Matthias Kläy
--
www.kcc.ch
"Matthias Klaey"
> You can see that in my original text where you have
> deleted words without using an ellipsis. Your "cite"
> me as saying:
>
> "Of course the anwer is very clear:"
>
> But my original text is:
>
> "Of course the anwer is (almost) every time very clear:"
I extend my apology for changing your meaning by snipping.
> My point is that you should use an ID - Content pair only
> after you have *proofen* that using the Content Only
> would degrade the application to a degree that is
> unacceptable for the client.
After some years of developing with Access every day, and of forming habits
in development techniques, it should be intuitive which approach to use.
And, the cost to the client of your development is a primary
consideration -- if using the pairs is your habitual approach, that is
likely to be best.
> And do not forget in this cosideration that every time you
> use the ID - Contents pair in a form or report you will
> have a table join that is absent in the Contents Only scenario.
Performance and response, as I pointed out, is going to be a factor only at
extremes of database size. And, even in the "large" client-server databases
I have worked on, none ever reach those extremes. YMMV. (As an example of
MV, I have a colleague and friend whose specialty requires very, very
complex tracking of "lots", "sublots", "sub-sublots", etc., and this, in
turn requires him to use a server datastore and to write pages and pages of
SQL from scratch; the kinds of applications that I address almost never
require writing SQL from scratch and none have ever included the kind of
complex retrieval he has to do -- we are quite content to acknowledge the
difference in application type and the difference in appropriate
implementation.)
> The very same applies to the "surrogate key" versus "composite
> natural keys".
For developer conveniece and design simplicity, I tend to be on the
"surrogate" side of the "surrogate vs. composite natural key argument".
Again, if your primary tables do not exceed hundreds of thousands of
records, the users perception of response is likely to be totally
unaffected, so I consider that I might as well use a surrogate key to
simplify development, reduce cost to the user, and simplify maintenance for
whoever comes along behind me to work on the DB.
I do not believe that surrogate keys violate relational design principles,
as some do. You may take a different view... I am convinced that an
"academic purist" approach does not lead to better-designed and implemented
database applications, only to arguments in certain newsgroups and
publications. Reasonable and rational tradeoffs make for better database
applications.
Larry Linson
Microsoft Office Access MVP
Matthias Klaey
news:po0ov3prhvtq5ct1v7s24h69uk37agmol5@4ax.com:
> Anyway, very seriously: Whenever I feel myself wanting to create a
> table with two fields, one an ID (Integer), and the second a
> "Content" (String), I ask myself: What is the ID used for? Can't I
> just as well use the "Content" field as the primary key? Of course
> the anwer is (almost) every time very clear:
>
> Abolish ID's!
>
> Then you will not fall into the trap of the
>
> Evil Lookup Fields
>
> because, miraculously --- it has disappeared!
I think you are asking yourself a very good question, but the answer
is not always going to be to eliminate the ID field. There plenty of
cases where you want a unique ID for a record with only one other
attribute, and that attribute can be edited. It's better to edit
that than to introduce the overhead of cascade update.
(of course, I'm a regular user of replication, and cascade update
can introduce serious edit conflicts, so I try to avoid it)
But in the case of a list of fruits, no, it makes no sense to store
an ID -- you're not likely to put in "eggplant" and decide later
that it should be "aubergine" (or vice versa).
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
You are quite right David, the cascading update is an issue I
certainly have noticed. The one major database I use it for us a
highly cascaded design with many many thousands of records (without
surrogate keys).
The primary reason for this particular db is to produce / maintain a
detailed product table that is in turn used by a variety of macro's in
Excel for report and analysis structuring. It is much easier and
faster to simply query a single table and bring back a disconnected
recordset. I also have some users that look at the data directly (not
my choice). It stops them from making screw ups by entering rubbish
that doesnt fit anywhere. So far it has been pretty stable and happy,
and damn fast on the data retrieval time. (<1sec over the network on a
good day, maybe 5 on a bad day).
I had to make a change to one of the 'Categories' (top of the tree)
that these products fall into, and it did take a while to do the
update, but still wasnt too bad from memory - its not like I was
waiting for hours or anything. Maybe a minute??? It was a time ago
now.
Cheers
The Frog