On 2018-06-17 23:43, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2018-06-16 13:31, Charlie-gm wrote:
On 6/15/2018 12:30 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
Some years ago I had created a solution where I had split the main data table into 5 separate tables, each with a 1:1 relationship. I kept the commonly held fields in the table, and moved others off like so: - Main Job table - Job Address table (job site address) - Job Dates table (key event dates for this job)
... [snip]
Based on my experience the fewer fields in a record (row) the better your performance will be IF you do not have to ALWAYS join the other tables. I think Ted touched on this already: trying to keep the field count small, but keep the most frequently needed fields in the same table. In general, JOINs are 'slow' (NOTE: the VFP ability to SET RELATION TO RECNO() pretty much obliterated JOIN performance - it really was like just having one huge table at your disposal - of course there were other things to worry about <g>, oh well....).
<snipped> > Last but not least, if you have it working now, it seems to be a tough > justification to redesign it just to "feel" better about less tables. > But in the end, whatever seems to be causing most maintenance > headaches is probably the best thing to target to change. So if those > multi-joins are constantly driving you crazy, then yeah, maybe some > consolidation is in order. But I recommend against a single, wide > (many columns), data record. It does impact performance. > > HTH, > -Charlie
You and Ted get exactly my POV. Exactly!
I had places the Notes (memo fields) in its own table (Job_Notes) and likewise sectioned off some of the other reporting fields in their own tables. I may have gained some performance but probably at the cost of easier coding.