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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|