I was told "Fields are expensive, records are cheap"
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
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.”
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
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”.