A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

I was told "Fields are expensive, records are cheap"



 
 
Thread Tools Display Modes
  #1  
Old February 25th, 2010, 12:44 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default I was told "Fields are expensive, records are cheap"

Hi,

I just have a self education question.

In response to one of my questions I stated that I added a lot of fields (a
lot was 30 fields) to my table. The initial implementation was so
successful, the user requested quite a few more enhancements resulting in the
addition of 30 additional fields.

One MVP who responded stated "Fields are expensive, records are cheap". I'm
currious about his statement. I'm new to Access (less than a year) but I
have over 30 years experience with relational databased on multiple
platforms. 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. We have a hardwired ethernet cat5 cable network.

Dennis

  #2  
Old February 25th, 2010, 01:04 AM posted to microsoft.public.access.tablesdbdesign
Rick Brandt
external usenet poster
 
Posts: 4,354
Default I was told "Fields are expensive, records are cheap"

Dennis wrote:

Hi,

I just have a self education question.

In response to one of my questions I stated that I added a lot of fields
(a
lot was 30 fields) to my table. The initial implementation was so
successful, the user requested quite a few more enhancements resulting in
the addition of 30 additional fields.

One MVP who responded stated "Fields are expensive, records are cheap".
I'm
currious about his statement. I'm new to Access (less than a year) but I
have over 30 years experience with relational databased on multiple
platforms. 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.


So you think rows are on the disk but fields are in RAM? Data has to be
retrieved from and written to disk regardless of this difference.

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. We have a hardwired ethernet cat5 cable network.


This is not specific to Access. It is a "best practice" in all relational
databases to have a normalized data design. This generally results in
taller, narrower tables as opposed to wider, shallower tables.

When table modifications call for lots of new fields it often means that a
one-to-many relationship that ought to be set up in multiple tables is being
shoe-horned into a single table.


  #3  
Old February 25th, 2010, 01:34 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default I was told "Fields are expensive, records are cheap"

Rick already responded to your questions ... but I'll add some additional
consideration ...

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.

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:

tblTable
TableID
YourNumber
YourDateTime

With this design, any time you need to add a new month/year, you don't add
another field, you add another record. This is where the "tall not wide"
comes from...

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.

"Dennis" wrote in message
...
Hi,

I just have a self education question.

In response to one of my questions I stated that I added a lot of fields
(a
lot was 30 fields) to my table. The initial implementation was so
successful, the user requested quite a few more enhancements resulting in
the
addition of 30 additional fields.

One MVP who responded stated "Fields are expensive, records are cheap".
I'm
currious about his statement. I'm new to Access (less than a year) but I
have over 30 years experience with relational databased on multiple
platforms. 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. We have a hardwired ethernet cat5 cable network.

Dennis



  #4  
Old February 25th, 2010, 05:15 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default I was told "Fields are expensive, records are cheap"

Rick,

Thanks for you responses, either I did not explain myself well or you missed
some obvious points in my statements.

Your comment: “It is a "best practice" in all relational databases to have
a normalized data design.

Response: This is true. I figured you would have picked up on that when I
stated I’ve been designing relational databases for over 30 years, which is
why I included that statement. But obviously I was wrong. Yes, relational
databases have been around for well over 30 years.


Your comment: “This generally results in taller, narrower tables as opposed
to wider, shallower tables.”

Response: This is true when you compare normalized tables to flat files.
However, I was not. I was comparing relational to relational. Therefore,
this statement has nothing to do with the question.

As for narrow tables, that depends on the applications. Most accounting
applications have pretty narrow tables. However, most personal lines and
commercial lines insurance policy and claim tables can get very wide.


Your comment: “So you think rows are on the disk but fields are in RAM?”

Response: Maybe you learned different computer science that I did, but
while this is a gross over simplification, your statement is ABSOLUTELY
correct. Rows are on disk and fields are in RAM. I know how other relation
database work, but I don’t know how Access works, which is what I am trying
to find out.

It is absolutely true that a program can only works with fields that are in
RAM. An application program can not work with a field that is on disk,
fields are only accessible while they are in RAM. The row has to be retrieve
from the disk and placed in memory in order for the VBA code to have access
to the row’s fields. Most database system provides the data to the program a
record or row at a time (excluding all of the look ahead and optimization
techniques.)

A program reads and writes rows (not fields) to the disk. I don’t believe
that VBA code can work on the entire row, unless there is a way to treat the
entire row as a string. I know that other databases have this capability, but
I don’t know if Access does or not.

When the file system write the row, except for blob and other such specialty
fields, the read and write functions within the database engine do not “know”
where one field ends and the next one starts. The simply read a record from
the table and place it in memory or they write a record / row to the table.

Given, the above I am truly puzzled by your statement. You make it sound as
if it this is not true when in fact that is exactly the way it works, even if
it is extremely over simplified.

Just out of curiosity, do you know of any application software that works
directly on the disk? Granted, the OS and OS utilities work directly on the
hard drive, but I’m not aware of any application software what does not.


Your comment: “Data has to be retrieved from and written to disk regardless
of this difference. “

Response: This statement is the point of my question! But one again, we
disagree. The difference in the disk access time it takes to retrieve one
record from the disk over a network versus retrieving multiple records. To
me that statement “Fields are expensive, records are cheap;” implies it is
better to have smaller row and multiple linked tables than it is to have one
row that contains all of the normalized data.

To me, this is ludicrous – unless there is something I don’t know about
Access. It seems to be that the only things that are achieved by breaking up
a large normalized row into multiple smaller rows in multiple tables in a
significant increase in disk access time and significant higher network
traffic. But then maybe Access is very inefficient in dealing with a lot of
fields and it is truly better to deal with slower disk access time than
Access’s overhead. I don’t know. That is what I am trying to find out.

Even given the increase in network and disk access time, CPU processing
speed and memory access time are still many, many times faster than access
the disks. Also, in most client-server systems I’ve had experience with; the
network traffic (data access) has been the bottle neck.


Your comment: “When table modifications call for lots of new fields it
often means that a one-to-many relationship that ought to be set up in
multiple tables is being shoe-horned into a single table.”

Response: Obviously you did not read my statement. This was something I
learned over 30 years ago. Third normal form tables do not contain repeating
fields unless they are a multi-valued field, in which case they are quite
useful in exploding parts for a completed assembly such as a car door, engine
or other such things. However, since Access does not support multi-valued
fields at this time, this is not an issue with respect to Access.

As I stated, the initial implementation was SO successful, the user
requested quire a few new enhancement that were beyond the scope of the
original project that we had to add 30 new normalized data fields. (I guess
I should have included that word).


Dennis

  #5  
Old February 25th, 2010, 05:19 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default I was told "Fields are expensive, records are cheap"

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

  #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

  #7  
Old February 25th, 2010, 08:48 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default I was told "Fields are expensive, records are cheap"

Duane,

Your comment: 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?

Response: You are correct is that I did not describe my table structure.
However, if I read someone had 30 years in relational db design experience, I
would have thought that he or she knew about designing to 3rd normal form and
would not have questioned them.


Your comment: 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.

Response: You are absolutely correct. Personally, I think the maintenance
number is higher. However, I did not interpret his comment this way for the
following reason. If you have to add a data field so you can produce a
report, what do you tell the user – I can’t do it because that field will add
5-10% to the cost and increases maintenance cost? What I have learned in my
30 years is the cost issue is NOT for the developer to decide, it is for the
user to decide. It is incumbent upon us to inform the user of the cost, to
develop a cost effective design, and maybe do a cost benefit analysis if the
numbers are big enough. However , if the user decides it is worth the cost,
then it is not our place to argue.


Your comment: This is why John Vinson often suggests "Fields are expensive,
records are cheap".

Response: As I said, I may have misinterpreted what John had to say. As I
stated in my above response, I though it had something do with the internal
workings of Access. I failed Mind Reading 101 when I was in college and have
not gotten any better over the year. All I received from John was the
statement “"Fields are expensive, records are cheap" with no explanation and
no background information regarding that that statement. As a matter of
fact, he included that statement in the same paragraph where he was asking if
the additional fields were repetitive fields. Given that he was referring to
repetitive fields, how was I supposed to make to giant leap that he was
referring to the development and maintenance cost of the fields and not to
the repetitiveness of the fields themselves?

Your comment: I took over a survey application at a hospital from a
programmer with many more years of programming than I had at the time.

My response: I don’t understand what this comment has to do with the
subject. I’ve come behind other programmer and rewrote the code so it work
better, faster, more flexible, and most importantly – more understandable and
easier to maintain. I had one customer who designed a series of program that
ran in a daily cycle. The only problem with the software is it took 30 hours
to run a daily cycle. When I was done, we were able to run the cycle in two
hours. But so what, that had nothing to do with the above question. I’m
sure other programmers will come behind what I’ve done and reworked my code
so that it is better. And I’m sure other have come behind you and reworked
your code. So I don’t understand the point of this comment.


Your comment: I'm surprised the basics have eluded you.

My Response: I’m surprised that the basics have eluded you also. I believe
you when you say that John uses that phrase when he is referring to the
development and maintenance cost of each field. In that respect I agree with
him. However, from a developer’s stand point, so what? The cost is not the
developer’s decision to make. I firmly believe that one of the basics in
this business is it is developer’s job to provide an efficient and cost
effect solution to the issue along with a realistic development estimate.
However, it is the user’s and only the user’s decision to determine if the
project is worth the development cost. It is their money and they have a
right to decide how to spend it, even if I disagree with them, which
frequently I do.


Your comment: 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.

My response: I don’t think offense is the right term. I think frustrated
because they did not read the entire statement that I took quite a bit of
time trying to phrase it correctly. Rather than go into all of the details,
table design, and background, I figure it I stated that I had the experience,
people would not be bringing up the issues that you would be with someone who
has just two weeks of relational databases. But, I was wrong. Maybe I just
think differently that other people. Personally, if a doctor tells me they
have been a doctor for 30 years, I don’t normally ask them if they can read a
thermometer. Would a doctor take offence to a question like that? Hmm,
probably.

Tell me something, if you as an MVP were to ask a similar question (which
you would not since you are an MVP) and someone ask if your data was
repeating or if you table was not normalized, would you not just shake your
head?

You have come to my aid quite a few times. I am very grateful for all of
you help. You also deserver a lot of respect for being an Access MVP.
However we are going to have to agree to disagree on the issues above,
especially who decides if a data field is worth the cost.



Dennis

  #8  
Old February 25th, 2010, 09:28 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default I was told "Fields are expensive, records are cheap"

Duane,

In another posting John clarified what he meant by "Fields are expensive,
records are cheap".


"What I consider "expensive" is that an improperly normalized table structure
will require far more work in getarounds, contorted queries, form and report
maintenance, and code than would a properly normalized design. "


John W. Vinson [MVP]


Dennis
  #9  
Old February 25th, 2010, 04:59 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default I was told "Fields are expensive, records are cheap"

I'm a little unclear about why you posted. It is unusual to add 30 fields to
a normalized table and still maintain the normalization, yet you seem
unwilling to describe the new need for 30 fields. It could be that this is
one of those unusual situations where a normalized table is broad and shallow,
but all we have to go on is that you have 30 years experience, from which it
seems we are to accept that the design is beyond question. Had you been
willing to describe the database's structure it could be that somebody here,
many of whom are well-trained and very experienced, could have suggested a
different approach. You could have accepted or rejected the suggestions, but
since that conversation did not take place it seems you posted to argue with
the contention that "fields are expensive, records are cheap".

To use your specific example of insurance processing, auto and home insurance
are so different that many developers would use a different table for the two
rather than broaden an existing auto insurance table to accomodate home
insurance. You could argue that it is all insurance and therefore is an
entity that belongs in a single table. You could similarly argue that
employees and clients are both people, and therefore should be in one table.
Many developers would define "entity" more narrowly, and therefore use
separate tables, in a situation where many fields are used for one type of
entity and not the other, or where the entities are clearly in different
categories.


Dennis wrote:
Duane,

Your comment: 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?

Response: You are correct is that I did not describe my table structure.
However, if I read someone had 30 years in relational db design experience, I
would have thought that he or she knew about designing to 3rd normal form and
would not have questioned them.

Your comment: 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.

Response: You are absolutely correct. Personally, I think the maintenance
number is higher. However, I did not interpret his comment this way for the
following reason. If you have to add a data field so you can produce a
report, what do you tell the user – I can’t do it because that field will add
5-10% to the cost and increases maintenance cost? What I have learned in my
30 years is the cost issue is NOT for the developer to decide, it is for the
user to decide. It is incumbent upon us to inform the user of the cost, to
develop a cost effective design, and maybe do a cost benefit analysis if the
numbers are big enough. However , if the user decides it is worth the cost,
then it is not our place to argue.

Your comment: This is why John Vinson often suggests "Fields are expensive,
records are cheap".

Response: As I said, I may have misinterpreted what John had to say. As I
stated in my above response, I though it had something do with the internal
workings of Access. I failed Mind Reading 101 when I was in college and have
not gotten any better over the year. All I received from John was the
statement “"Fields are expensive, records are cheap" with no explanation and
no background information regarding that that statement. As a matter of
fact, he included that statement in the same paragraph where he was asking if
the additional fields were repetitive fields. Given that he was referring to
repetitive fields, how was I supposed to make to giant leap that he was
referring to the development and maintenance cost of the fields and not to
the repetitiveness of the fields themselves?

Your comment: I took over a survey application at a hospital from a
programmer with many more years of programming than I had at the time.

My response: I don’t understand what this comment has to do with the
subject. I’ve come behind other programmer and rewrote the code so it work
better, faster, more flexible, and most importantly – more understandable and
easier to maintain. I had one customer who designed a series of program that
ran in a daily cycle. The only problem with the software is it took 30 hours
to run a daily cycle. When I was done, we were able to run the cycle in two
hours. But so what, that had nothing to do with the above question. I’m
sure other programmers will come behind what I’ve done and reworked my code
so that it is better. And I’m sure other have come behind you and reworked
your code. So I don’t understand the point of this comment.

Your comment: I'm surprised the basics have eluded you.

My Response: I’m surprised that the basics have eluded you also. I believe
you when you say that John uses that phrase when he is referring to the
development and maintenance cost of each field. In that respect I agree with
him. However, from a developer’s stand point, so what? The cost is not the
developer’s decision to make. I firmly believe that one of the basics in
this business is it is developer’s job to provide an efficient and cost
effect solution to the issue along with a realistic development estimate.
However, it is the user’s and only the user’s decision to determine if the
project is worth the development cost. It is their money and they have a
right to decide how to spend it, even if I disagree with them, which
frequently I do.

Your comment: 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.

My response: I don’t think offense is the right term. I think frustrated
because they did not read the entire statement that I took quite a bit of
time trying to phrase it correctly. Rather than go into all of the details,
table design, and background, I figure it I stated that I had the experience,
people would not be bringing up the issues that you would be with someone who
has just two weeks of relational databases. But, I was wrong. Maybe I just
think differently that other people. Personally, if a doctor tells me they
have been a doctor for 30 years, I don’t normally ask them if they can read a
thermometer. Would a doctor take offence to a question like that? Hmm,
probably.

Tell me something, if you as an MVP were to ask a similar question (which
you would not since you are an MVP) and someone ask if your data was
repeating or if you table was not normalized, would you not just shake your
head?

You have come to my aid quite a few times. I am very grateful for all of
you help. You also deserver a lot of respect for being an Access MVP.
However we are going to have to agree to disagree on the issues above,
especially who decides if a data field is worth the cost.

Dennis


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201002/1

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.