The mantra in the subject line is purely a rule-of-thumb for beginning
database designers. It has no bearing on your question about data access.

Your question revolves around how the data is physically stored on the disk.
You're saying that there is a delay to move the disk heads to different
physical locations to retrieve records, and that delay represents degraded
performance.

While there's some truth in that simple view, the reality is much more
complex than that. Access uses pointers to store the data. If you define a
Text field as 255 characters but enter 2 characters in it, it does not use
up 510 bytes (unicode) of disk space to store those 2 characters.
Internally, the database keeps track of where each field starts in each
record. Some fields (memo, OLE, attachments, MVFs) are not actually stored
in-line with the other data in the record either. So it's not a matter of a
single record being faster to retrieve. There are many other factors at work
here, including whether the database has been compacted recently, whether
you are using page- or record-locking, whether the disk is badly fragmented,
how intelligent are the various layers of caching (physical on the drive, or
the controller, or the operating system, or in Access), the Rushmore
algorithms in JET, and so on. Then we may have to start another mindset to
handle SSD storage issues as well.

In the end, you don't really need to consider these as factors when
designing JET tables. It makes no sense to break a 60-field table down into
one main table with several one-to-one relationships just for performance
reasons. The management of those relationships alone represents unnecessary
complexity for the database and for developer. You may be forced to go that
route if you are subclassing, or if your record is too wide to fit into the
4K page size; otherwise don't even consider it (which is probably what you
were thinking in posting.)

Having said that, having 60 fields in one table is unusual. The database I
happen to be working on right now has 93 tables, and none of them has more
than 32 fields (including 6 for tracking when and by whom the record was
created, most recently edited, and deactivated.)

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


"Dennis" wrote in message
...
Hi,

First let me apolozie for the empty question below. I hit the Post button
my mistake and it posted a blank question. Sorry.

Hi,

I am restating my question because based upon the responses I receive I
obviously stated my question incorrectly. So please let me try again.
Hopefully I will be a bit more sucessful this time around.


In a previous question, I stated that I added 30 fields the membership
table
(one of many in the system). The initial implementation was SO
successful,
that the user requested quite a few enhancements resulting in the in a
HUGE
increase in the scope of the original project. These changes resulted in
the addition of 30 new fields to the membership table.

The last time people responded to my question, they were concerned about
the
whether or not these were normalized fields. Please let me state that the
row has been normalized to 3rd normal form and these fields are NOT
re-occurring fields.

One MVP who responded to my original question stated "Fields are
expensive,
records are cheap".

Maybe I am misinterpreting his comment. But to me this means that it is
better to have smaller row in multiple linked tables than it is to have
one
large row that contains all of the normalized data. (IE – it is better to
split a large normalize row in a single table into multiple rows in
multiple
tables). I hope I explained that the right way.


My question pertains to the difference in the disk access time it takes to
retrieve one record from the disk over a network versus retrieving
multiple
records versus the overhead involved in Access parsing a lot of fields out
of
a large row or parsing the same number of fields from multiple smaller
rows.


I've always been taught the exact opposite - that "Fields are cheap,
records
are expensive" since going to disk is so SLOW versus accessing data in
memory.

Is there something different about Access where the statement "Fields are
expense, records are cheap" is true? I'm using Access on local machine
where
the front and backs end reside on the same machine as well as having
multiple
front ends on each client's machine tied into the back end which resides
on a
file server over a cat 5 hardwired Ethernet network.

My question is strictly concerning the data access time of multiple row
over
the network versus Access’ overhead of extracting data from multiple small
rows versus one large row. And we are assuming a 3rd normal form database
design.

And it may well be that I am totally misinterpreting the “Fields are
expensive, records are cheap” comment.

Thank you for your comments.



Dennis