View Single Post
  #10  
Old February 25th, 2010, 05:18 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default I was told "Fields are expensive, records are cheap"

Dennis

Sorry I wasn't more explicit... see responses in-line below

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


Please re-read my response. It starts with the word "If". I was describing
the workload/maintenance of an overly-simplified design, and pointing out
that adding fields is expensive, in terms of the maintenance it requires to
all affected objects.

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?


That would depend on what data elements the user & I agreed were needed.


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)


That sounds like how I try to design systems.


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.


If you are looking for other folks ideas, to compare them with yours and
decide what "balanced" approach would work best for you, let us know. You
asked for an assessment of John V.'s statement.

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.


Maintenance is maintenance, whether on one object or several. My comments
were intended to offer the option of a design that would require NO
additional maintenance, since the table would grow longer, not wider.



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.


That you've learned not to do this was not evident in your original post.
Responding as you have could be interpreted as 'baiting' folks, offering an
incomplete description and then criticizing folks for not inferring or
knowing your detailed situation.

If you want detailed suggestions/ideas/approaches, provide detailed
descriptions.


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


Define "expensive".

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.