Lookup fields in tables bad?

=?Utf-8?B?RnJlZA==?= wrote in
:

The "my mom" part was metaphorical, but your post didn't cover the
real example I gave......there are lots of databases out there
where there is no developer, and which are created in minutes and
used by only on or two power users. The don't look at data in
table views, but often look at it query views, setting completely
different sort and filter criteria on different fields with each
use.

I did mean my response to address that scenario -- those kind of
uses should create forms to edit their data. Period. It's the only
way to do it right in the long run.

And, yes, I know it's tilting at windmills to fight this fight in
that user population, as they are going to use the tools MS has
provided (and encourages the use of by their implementation). I
think MS is wrong in what it has implemented, and does a terrible
job in its sample databases of implementing "easy" things that are
really bad design in the long run.

The fact that people will use it and feel like it's useful does not
change the fact that there are ways to do their work that are no
harder to implement and don't have the long-term downside.

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

Hi John,
Thank you for the replies; yours and others have helped me a lot. I've tried
to reply in sequence with this thread but I guess I'm not smart enough to do
that yet...

Is there a discussion group for that? :-)

Best regards,
Robert

"John W. Vinson" wrote:

On Thu, 22 Jan 2009 20:33:01 -0800, Robert5833
wrote:

I’d like to think that I only use lookup fields in tables when they are
foreign key references, but just given the quantity of them in my tables I’m
not sure I understand when a foreign key could be a text box, or when it must
be a combo box with lookup? (I would be grateful if you or someone could
clarify that point for me; what is the rule, logic, or rationale for one or
the other.)

A foreign key can *never* be a textbox, nor can it ever be a combo box!

Textboxes and combo boxes are display tools. Keys (primary or foreign) are
fields in a Table. Those fields can be displayed - on a form or report, or
thanks to some questionable design decisions by the Access development team,
in tables - but don't confuse the data *storage* with the data *presentation*.
Primary and foreign keys are logical entities defining the relationships
between data.

A Primary Key is a field - or a set of up to ten fields - which uniquely
identify a record in a table.

A Foreign Key is a field - or a set of up to ten fields - containing the same
data as the Primary Key of some other table, and serving as a link to that
table.

Any field (whether it's a primary key, a foreign key, or not used as a key at
all) can be displayed using the various data display tools that Access
provides - or for that matter, not displayed at all. For instance, if I use an
autonumber primary key (which I do), it will NOT be displayed to the user at
all; the same applies to the Long Integer foreign keys which are related to
it.

The natural key/surrogate key argument can get long and heated. I'll use
natural keys when they're short, stable, and unique, which actually leaves out
a lot of fields that otherwise might be candidates. People's names, for
example, fail on all three counts! Some examples where natural keys may be
appropriate are State two-letter codes (if your addresses are entirely or
mostly in the United States and Canada); there's only one state/province with
ID as a code, two letters is suitably short, and they rarely change (Canada
added NU and NT, Nunavut and Northwest Territory, a few years ago); five digit
ZIP codes are another reasonable choice.
--

John W. Vinson [MVP]

Great response. Good to know it's all helpful.

Hope you're over the 'flu quickly.

--
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.

"Robert5833" wrote in message
...
Hi Allen; et al,

Thanks so much for the great outline on this subject, and I appreciate
very
much your taking the time to help me understand.

I would have replied sooner, but I’ve been down hard with the flu.
[snip]