DWUTKA at marlow.com
DWUTKA at marlow.com
Tue Mar 30 13:57:26 CST 2004
Ken, you are absolutely right. In math, one example of failure, proves a
formula wrong. However, you are incorrect on the engineering example.
Let's start with math. Your original posts said that Lookups bloated the
database, and were a performance issue. Those were mathematically based
arguments. I proved them wrong. For you to prove me wrong, and forever
vindicate your arguments in those areas, you would need to show proof of
their bloat/performance (under normal conditions, no other variables in the
mix....). And before you try, keep in mind, that using a just a value list
is just as valid a method of using lookups, as using a lookup query/table.
So you'll need to show how ALL lookups are speed/size issues. (I did my
testing with query/table lookups. No difference in speed or size, then if
they were just relationships.
Now for engineering. Engineers design things to fall within set tolerances.
Everything they design have failure points. In fact, a lot of designs have
built in failure points, to allow for non-crucial elements to fail, and
reinforce crucial elements. Take the Challenger incident. Those O-Rings
had FAIL POINTS at lower temperatures. The engineers knew, and they even
warned NASA, but it was political dancing that killed the Astronauts, not
the engineers. The people operating everything did not heed the engineering
limits, and thus suffered the consequences. Take 9-11. The architects of
the WTC never designed the towers to withstand a head on collision with a
jet airliner. They WERE, however, designed to handle being clipped by one.
You can't build anything, to compensate for every possible scenario.
Now, if you follow that, take a look at your argument again. You are
talking about later generational development. You are NOT talking about
single generation development, which is where databases are originally
designed. In the first place, Lookup's are not always an issue with later
generational development. Lookup's are merely default controls. Now yes,
if I'm doing a funky 'mergence', where I am not using all of the tables,
then I have a LOT of stuff to look into. But if you take an older database
BE, and go to create a new FE, the lookups are going to help. (If a single
failure mode prove it's bad, doesn't a single helpful mode prove it's good?
LOL).
>Drew, you said on 3/26/2004 that "the problem is that Lookup fields do NOT
>cause corruption.". I have now proven two cases where they do.
They don't. Corruption is when a databases file structure fails in some
manner. It is not mis-matching of data, because that is human failure, NOT
database corruption. You might say that it could be 'data corruption',
however, in the cases that you have marked, the data wasn't changed, the
relationships were setup wrong. Again, a design flaw, NOT corruption. You
really need to watch the lingo on that. Saying that a feature causes
corruption, is a very strong thing to say. When in fact, I have never heard
of a single Access feature that actually causes database corruption. Every
database corruption I have ever come across (and have ever heard of on the
list), had an outside (non-Access) influence. NIC's, faulty hardware,
sunspots, etc.
I'm going to give you a cross example to yours. Because in the first place,
it wouldn't have mattered if the State field was a lookup or not, if they
only imported the Address table, and used THEIR State table (lookup or not),
they would have gotten the WRONG data. Yes, the lookup would SHOW them the
wrong 'linked' data in datasheet view, but so would a query based on the
same 'wrong' relationship. Don't see how Lookup's CAUSED that failure.
Unthorough developers caused it.
Here's my example. I have an Access database that is used in our company's
website shopping cart. We use an Oracle database for our production
database. Now, the company wants to take the 'website' customers, and
import them into the Oracle database. The Oracle FE is a goofy system
written in PROIV, but I can easily get at the Glovia tables. Now, I go in
and find the table that obviously contains our customers. I match the
fields up, and start pushing data through. Sooner or later, all sorts of
things don't add up correctly.
Why is that?
Simple, the Oracle system has a SPROC, which is run by the FE when a new
customer is entered. Relevant customer data is sprinkled all over the
place, not just the main table. Because I am just putting data into the
main table, and not running the SPROC, my data is not being 'sprinkled', and
therefore, begins to cause LOTS of problems down the line.
Now, when I look at the linked table from within Access, do I know that the
table is supposed have a SPROC run, when data is entered? Yes, if it was
setup as a trigger, the database would have done it itself, unfortunately,
the original developers decided they wanted the FE to 'control' when the BE
does stuff, so there are practically no triggers in Oracle for that system.
Does this mean that SPROCS are bad? Because they weren't used correctly,
that makes them the single point of failure.
OR, does it mean that as a developer, I should have investigated whether a
SPROC was supposed to run or not (which I did by the way). By your
examples, I should have just played the idiot, and thrown my hands up in the
air screaming 'Damn you SPROCS!'. Sorry, that was meant to be funny! LOL.
Ken, all I have been saying, and am still saying, is that with the one
exception of that form filter issue, there is nothing wrong with the
designed function of the DefaultControl property. I have shown that if you
are really concerned about them, that after initial design, they can be
easily stripped from all of your fields with a little bit of code. I have
shown that there are no performance or size issues involved when using this
property. Many people have chimed in with examples of how they find them
helpful. Does this mean you have to use them? Of course not. If you don't
like to use them, DON'T. But don't tell me, that I'm am wrong for using
them, when you can't prove that they are a speed/size concern, or when you
can't show how they fail in the database they were designed for.
Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Ken Ismert
Sent: Monday, March 29, 2004 10:42 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Lookup Fields in Table Design
Andy: You'll be happy to hear that this is my last post on this subject. I
had intended to rest after my last post, but since John has asked me to
clarify my position, I will do so, as succinctly as possible.
My remarks will fall under three topics: general response, how table field
lookups (TFLs) can compromise relational integrity, and a summary of my
failure analysis.
General Response
================
I suppose my mindset is different from my opponents. In mathematics, a
postulate, no matter how seductive, can be disproven by a single
counter-example. In engineering, a structural design, no matter how
beautiful, will have to be corrected or compensated for if a single failure
mode can be demonstrated.
The data we engineer is no less real than lemmas or steel. My first
responsibility is to the integrity of the data entrusted to me. Thus,
failure analysis matters to me.
Given a choice between a path that offers risk, no matter how slight, and
another which offers equivalent results with no risk, I will take the latter
every time. My duty to the data demands it.
Drew, you said on 3/26/2004 that "the problem is that Lookup fields do NOT
cause corruption.". I have now proven two cases where they do. You have made
your points. But your refusal to allow for even a caveat on this topic is
intellectually dishonest. You simply cannot claim that you are 100% correct.
Why is this so hard for you to admit?
But seriously, with your cavalier dismissal of potential causes of failure,
I'm glad you didn't choose to be an engineer. I sure as hell wouldn't want
to drive across any bridge you design. Of course data errors matter, if not
to you, then certainly to the owners of the data.
But, rather than follow my opponent's example of loudly and longly
belittling the other side, adding nothing but rehashed versions of what he's
said now dozens of times before, I have two NEW items to contribute.
How TFLs Compromise Relational Integrity
========================================
Take two US divisions of a company: East and West. This company had a better
data designer, who defined tblStates with three fields: ID - autonumber,
Code - text, and Name - text. Better still, referential integrity was
established with the ID foreign key in tblAddresses. Of course, TFLs were
defined for the table/lookup pair.
The only small, tiny flaw is, the data was entered differently for each
division: East entered theirs in Name order, while West did theirs in State
Postal Code order. So now we have two tables, with identical logical
content, that have different IDs for most of the records. But both sets of
IDs are numbered 1 to 50.
So, the fateful day comes along when East wants to do a nationwide mailing,
and requests West's database. West sends their copy, and East simply links
West's tblAddresses into their front-end, creating tblAddresses1.
Opening up tblAddresses1, everything looks great: every record has a state
code -- no missing data. East does their mass mailing using a query off of
tblAddresses1, and gets back a huge pile of returns for bad addresses.
What has happened, of course, is the TFL in West's tblAddresses has
cross-linked with East's tblStates, with autonumber IDs that transposed the
state codes.
Naturally, if East decided to help West and enter new addresses, relational
integrity would allow it, because it cares only that the foreign key exists
in the proper lookup table, not what it represents.
The large potential weakness here is that if you look at any two tables with
autonumber keys, there is almost always a large overlap in autonumber ID
values.
Under the right conditions, if you cross-link a smaller table to a larger
table via TFLs, you might never notice until you have hose your data, ruined
an audit, etc.
So, TFLs can defeat even referential integrity.
TFL Failure Analysis
====================
In front-end/backend applications, TFLs fail when their external context
assumptions are violated.
Assumption 1: The external lookup table will always exist.
Path of failure: You don't link the lookup table in the
front-end, or rename the link. No table with the
same name exists.
Consequences: Intermittent errors. Queries relying on TFL
don't show expected values.
Assumption 2: The external lookup table will always match the
internal name.
Path of failure: The correct lookup table is linked when
another table of the same name exists, or is not
linked when a table of the same name exists
Failure Mode 1:
One or more fields referenced in the lookup SQL
do not exist.
Consequences: Errors. Lookup values not properly displayed.
Failure Mode 2:
All fields referenced in the lookup SQL exist.
Consequences: No errors. Inconsistent/incorrect data
displayed.
* If no referential integrity defined, data entry is
possible using values from the wrong lookup table.
* If referential integrity is defined on Autonumber
ID's, integrity will be defeated where the ID in
the wrong lookup table has a match in the
correct one.
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com