[AccessD] Lookup Fields in Table Design

John Bartow john at winhaven.net
Sun Mar 28 13:02:36 CST 2004


True.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
DWUTKA at marlow.com
Sent: Sunday, March 28, 2004 4:00 AM
To: accessd-bounces at databaseadvisors.com; accessd at databaseadvisors.com
Subject: RE: [AccessD] Lookup Fields in Table Design


Well it's hard to argue with that.  But then again, how many database
systems (which you pay for), hand out their actual inner workings?

Technically speaking, you can get to your data without JET, it's just not as
well organized as it is WITH JET.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
To: Access Developers discussion and problem solving
Sent: 3/27/04 3:16 PM
Subject: RE: [AccessD] Lookup Fields in Table Design

Yes, so I will now change:
"Data goes into black box - gets corrupted - data doesn't come out of
black
box."
To:
"Data goes into gray box - gets corrupted - data doesn't come out of
gray
box."
;o)

It did help though explain the issue sayers regain +.5

Score: nay-sayers 4 / sayers 8

John

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
DWUTKA at marlow.com
Sent: Friday, March 26, 2004 1:28 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Lookup Fields in Table Design


I'm shocked.  I used Dev's site quite a bit, when I started out with
Access.
I really admire a lot of what he has done.  However, I think a lot of
what
he has about Lookup Fields on there are wrong.  I just changed my test,
from
a simple State lookup, to a StateID --> StateName setup. Added an ID
(autonumber) field to tblStates, and then updated the data in my 'main'
table to have the ID's, instead of the abbreviation.  Then I switched it
to
a number field.  I then copied the database again, and switched the copy
to
be a lookup field for the StateID.  Compacted both (a little over
200,000
records in both), and NO difference in size.

Without indexing... querying is the same speed, with indexing, the
querying
is still the same speed.

However, I now noticed a difference in size, with a lookup field, when
the
field is indexed.  That is definitely interesting.   However, that goes
away, if you use a callback function.

VERY interesting.  Have to give them half a point back, because there is
a
small amount of 'bloat', if you use a query/table for the lookup.  But
it's
negligible, and also, both databases grow when you index the field, just
grows a bit more.  I would never really call that 'bloat'.  Not trying
to
back track, but to me, bloat is when a database grows in size over time,
but
when compacted, it gets smaller.  The portion that is 'reduced' is
bloat.
The portion left after the compact is 'overhead'.  That's my definition.
Admittedly, if you are going to run close to max db size, then lookups
should be removed, to get that much more space.  But we are talking
overhead
space, not really 'bloat'.

As far as the compacting issue, that's another story.  Microsoft has
several
good reasons for not releasing the inner workings of JET.  First of all,
there is the licensing issue.  If you developed a software package you
wanted people to buy, would you give anyone the source?  Of course not.
If
MS gave out the inner workings of JET, it wouldn't be long until Access
was
unnecessary, because people would just roll their own JET engine.
Another
reason is support.  Jet does a lot of stuff, so even if they did hand
out
the detailed specs, one little slip, and even a good developer could
seriously fry an .mdb.

There is nothing mysterious about corruption.  It's usually easy to
explain,
and in most cases is fixable.  What you must remember, is that file
corruption is NOT limited to .mdb's.  It can happen in any type of file.
The difference between Access and other file types, however, is what
makes
it seem like more of an issue.  A few years ago, our network was in a
serious state of disrepair.  We had file corruptions all over the place.
That included Word and Excel files, and definitely included .mdb's.  A
file
is nothing more then a series of bytes.  With a bad network, flaky hard
drive, corrupted OS, (and a million other reasons), it certainly is not
impossible to goof a few bytes here and there.  Now, depending on what
kind
of file it is, and what that file does, is going to determine how the
corruption affects things.  Take, for example, an .exe or .dll.  Those
files
contain CPU instructions.  Have you ever had a program start crashing on
you?  After an Uninstall and reinstall, everything is working again.  A
lot
of times that is due to a file corruption.  One corrupted (changed) byte
in
a .dll or .exe, and you are now changing how the CPU deals with that
code.
All sorts of things can happen.  However, in something like and HTML
file,
HTML is designed to ignore what it doesn't understand, so it's nearly
impossible to have normal file corruption do any real 'damage' to an
HMTL
file.  The more common issues with Word and Excel are the 'locking'
files.
Something will goof in them, and then that file is locked out.  Since
Word/Excel is saving changes in those temp files, actual corruption in
the
original document is more rare (but not unheard of...excel and word have
to
update the original document when you save it, so that is when
corruption
can occur there).

With an .mdb file, JET is monitoring who has what locked, is
reading/updating indexes,  reading/updating data, etc.  A lot of
activity in
one file.  The .ldb file is only storing who's logged in (and it is also
used for locks within the .mdb, but other then the who's logged in, no
other
data is being changed in the .ldb, only locking bytes).  Get a goofed
byte
in the mix, and things can either continue, go seriously awry, or
somewhere
in between.  just depends what was affected.  A lot of time, the
corruption
is simply someone's machine not 'closing' out of the database correctly.
That is easily repaired.  But if bytes are corrupted in the indexes, the
table definitions, or in the code, that is something the system doesn't
know
how to 'replace', because it doesn't have a 'template' or 'starting
point'
to replace it with.

As far as I know, the ONLY design issue KNOWN to corrupt a database,
happens
to lie with a problem between JET and Foxpro.  Something about the
differences between how JET deals with Memo fields, and Foxpro deals
with
them.  Access does NOT corrupt .mdb's, it is an outside influence that
does
that.  That is a VERY important concept to remember.  Because of that,
there
are NO features within Access that will corrupt a database.  (Yes, a
feature
could be programmed wrong, but Microsoft would eventually fix that with
a
service pack, because a self corrupting database system won't sell....).
Recovering from a corruption all depends on where the corruption
actually
lies, which is not entirely blind luck.  Since most corruption issues
are
only put into affect when a database is being read/locked/written, what
you
are reading/writing is usually going to be the place where the
corruption
lies.  So it could be data within a table, table defs, indexes, code,
etc.
Practically everything used within a database.  So not only are all
database
features designed to NOT corrupt the database, but they are also all
prone
to fail, if file corruption does occur. On top of that, all features
have
their own quirks as to the difficulties of recovering.  Corruption in
the
data isn't too bad, since you'll just get a bad record or two.
Corruption
in code is more difficult, because it will probably lock up quite a bit
of
the code, but that can be returned.  Corruption within a tabledef, or
index
can affect whether the data is easily retrievable, since JET uses them
when
retrieving it.

Hope that clears up some of the mystery for you.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Bartow
Sent: Friday, March 26, 2004 10:39 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Lookup Fields in Table Design


Hi Drew,
Don't misunderstand me, your points are well taken, and you have my
utmost
respect as a developer/programmer.

Corruption is just a hot spot on my psyche. If I knew HOW it was caused
it
would be different and that's the issue - apparently no one (including
M$)
does or they would prevent it. Even when considering that M$ allows some
things to happen/not happen as a bone thrown to its third party
developers,
no one has a corruption fixing utility that works and is making money
off
of.

"Data goes into black box - gets corrupted - data doesn't come out of
black
box."

That's actually my biggest concern with Access. The absolute (publicly)
undocumented proprietary nature of it. Sometimes I think the (publicly)
can
be left out of the statement.

I'm actually quite surprised by the results of this little debate.  Ken,
Charlotte, Dev, Lebans and others all warn against it. I think the
question
I posed here to Susan concerning lookups originally came to mind about 5
years ago when I saw it on Dev's website under the "ten rules". I don't
follow "rules" - when I see the word "rule" my mind automatically
translates
it to "strong guideline", but it made me curious. Like I said I don't
generally use lookups, I have, and I will if the situation calls for
them
again.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
DWUTKA at marlow.com
Sent: Friday, March 26, 2004 1:17 AM
To: accessd-bounces at databaseadvisors.com; accessd at databaseadvisors.com
Subject: RE: [AccessD] Lookup Fields in Table Design


John, the points are cute, but the problem is that Lookup fields do NOT
cause corruption.  He said they made it more difficult to fix.  There
are a
TON of things (that everyone uses) that make repairing corruption more
difficult.  Essentially, if you make a blank database, and put it on CD,
you
stand the best chances of recovering that database when it's completely
blank, and never used.  Once you start adding data, code, objecs, etc,
you
are adding things which can complicate a recovery.  So it's a no pointer
/
moot point, to say that lookup's made a particular recovery difficult.
If
there had been a bit of error handling code, which got in the way of
recovering a database, would you say 'I'll never use Error Handling
code,
because it made my life difficult once'?

I didn't think so.

Drew


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com






More information about the AccessD mailing list