View Single Post
  #6  
Old February 25th, 2010, 06:00 AM posted to microsoft.public.access.tablesdbdesign
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default I was told "Fields are expensive, records are cheap"

Dennis,
I don't think you have ever described your table structure(s). It very well
may be that your tables are normalized. How would we know other than to
guess that you know what you are doing?

I think the "Fields are expensive" was described in that each field
generally requires a certain amount of maintenance. Let's say you have a
table with 10 fields. Generally each of these fields requires controls on
forms and reports, columns in queries, and perhaps other time (expense) to
create. Adding another field would generally add 5-10% to the expense of
either creating or maintaining some of these objects. This is a very basic
concept that someone with 30 years of experience creating relational
databases should understand.

I took over a survey application at a hospital from a programmer with many
more years of programming than I had at the time. She had created a single
field for each question in the survey. This required tons of work (expense)
in creating and maintaining the application. My efforts resulted in the
basis for At Your Survey
http://www.rogersaccesslibrary.com/f...ff36 f3577dec
which provides great functionality at much less expense yet more records.

This is why John Vinson often suggests "Fields are expensive, records are
cheap". I'm surprised the basics have eluded you. I expect you took offense
to the statement when you should not have. You should always feel free to
post your structure to support your efforts.

Duane Hookom
MS Access MVP

"Dennis" wrote in message
...
Jeff,

Your comment: If your application involved only one table, and one query,
and one form, and one report, and one procedure, when you changed that
table
from 30 fields to 60, didn't you also have to modify (i.e., maintain) your
query, your form, your report, and your procedure?

Imagine how much maintenance there'd be if you needed to change it again?!
.... or if you had more than one of each.

Response: What you say is true, but I’ve re-read my question / statements
and no where did I say I had one table, one query, and one form. So I don’t
understand what this has to do with my question.

The original db contained 4 different tables, 20 queries, 6 forms, and 10
reports. After the enhancements, I have about 7 different tables, 45
queries, 15 forms and sub-forms, and about 30 reports.

On all of the system on which I have worked, whenever the user request
significant enhancements and expansion beyond the original scope, there is
significant maintenance. In this case, the enhancement did not affect the
existing reports or half of the existing forms so there was not
maintenance
there.

Let’s say that you were asked to write a personal auto policy processing
system, which you did. The client was so happy that they then said the
wanted to expand the system to also write home owners insurance. Would
that
not be a significant enhancement that might cause you to added quite a few
additional fields to existing tables as well as many new tables?

In the 30 years I’ve been in the relational database field, I’ve learned
to
design systems so that “A good database structure finds the right balance
between the rigidity that prevents bad data (relational integrity,
suitable
data types) with flexibility that copes with unforeseen possibilities.”
(Allen Browne)

As a result, the unforeseen changes on the user part did not alter the
database design. I simply added new fields to existing tables and new
tables
where needed. Adding new fields to a table or query does not require any
changes to existing forms or reports that utilize that query but do not
utilize the new fields. So there was no maintenance to those forms or
reports. There was only maintenance issues I had to address were on those
forms, queries, and reports that I had to change to enable the new
enhancements.



Your comment: On the other hand, if what you are using multiple fields to
store (hint: this is a spreadsheetly way to organize the data) were
"repeating values" ( a prime example is mmm-yy ... as a field name, to
store
numbers for a given month and year), then you could modify your table
structure to hold:

Response: Again, I wish you had read my introduction where I state I have
30 years of relational database design experience. As I stated in my
response to Rick, this was something I learned NOT to do over 30 years
ago.

The really sad part of all this is, neither one of you addressed my
original
question whether “Fields are expensive, records are cheap”.

Dennis