(VFP9SP2 app with MySQL backend)
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) - Job Notes table (all text/memo fields, with key field of course) - Job People table (folks assigned to the job)
On review, I think this just made more work and I should consolidate all of these fields inside the single Job table.
Your thoughts on either design approach?
tia, --Mike
Well - since there is in theory only 1 Addr/job - but, each job can have multiple Dates/Events as well as multiple Notes & People/job - in theory your approach seems to make sense. If Notes are related to People - you could maybe consolidate them. Just my $0.0225 input - adjust up for inflation...
On 6/15/2018 12:30 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
(VFP9SP2 app with MySQL backend)
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) - Job Notes table (all text/memo fields, with key field of course) - Job People table (folks assigned to the job)
On review, I think this just made more work and I should consolidate all of these fields inside the single Job table.
Your thoughts on either design approach?
tia, --Mike
[excessive quoting removed by server]
Normalize the data is something we are taught and usually, fail to do. Here you have done that and now you want to flat file it instead?
Only one note, address or person involved in a job? Maybe this is for billing a weld and not a bridge?
On Fri, Jun 15, 2018 at 11:30 AM < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
(VFP9SP2 app with MySQL backend)
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)
- Job Notes table (all text/memo fields, with key field of course)
- Job People table (folks assigned to the job)
On review, I think this just made more work and I should consolidate all of these fields inside the single Job table.
Your thoughts on either design approach?
tia, --Mike
[excessive quoting removed by server]
You are supposed to design the ER using the best normal form, at least according to school textbooks! :)
On Sat, Jun 16, 2018 at 12:30 AM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
(VFP9SP2 app with MySQL backend)
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: .... On review, I think this just made more work and I should consolidate all of these fields inside the single Job table.
Your thoughts on either design approach?
On Sat, Jun 16, 2018 at 12:53 AM, Man-wai Chang changmw@gmail.com wrote:
You are supposed to design the ER using the best normal form, at least according to school textbooks! :)
But you don't just design it once, you design the perfect logical design, then you design a physical design that takes into consideration real-world issues such as storage and performance. So, logically, all fields might fit in one table, but realistically, you might decide to store less-used or overly large data in a separate 1:1 table.
In this case, there's a 1:1 table that *COULD* be folded back into the original. You have to evaluate the cost of migration: rewriting the code, writing the migration, supporting customers with both schemas in production out in the field, against the benefits. In a lot of cases, a legacy design is best left as is. "Things are the way they are because they got that way."
Usually the situation is the opposite: an attribute once included in the table needs to be extracted for a 1:M relationship because the client told you "There's NEVER more than a primary and alternate contact" and that turns out not to be the case after a while, or the business changes.
Or an even simpler approach of 'if it aint broke, don't fix it'. Particularly in legacy systems where documentation is out of date or missing and some code does things you don't necessarily understand, just don't touch it.
-----Original Message----- From: ProFox profox-bounces@leafe.com On Behalf Of Ted Roche Sent: Saturday, 16 June 2018 7:47 PM To: profox@leafe.com Subject: Re: Database design
On Sat, Jun 16, 2018 at 12:53 AM, Man-wai Chang changmw@gmail.com wrote:
You are supposed to design the ER using the best normal form, at least according to school textbooks! :)
But you don't just design it once, you design the perfect logical design, then you design a physical design that takes into consideration real-world issues such as storage and performance. So, logically, all fields might fit in one table, but realistically, you might decide to store less-used or overly large data in a separate 1:1 table.
In this case, there's a 1:1 table that *COULD* be folded back into the original. You have to evaluate the cost of migration: rewriting the code, writing the migration, supporting customers with both schemas in production out in the field, against the benefits. In a lot of cases, a legacy design is best left as is. "Things are the way they are because they got that way."
Usually the situation is the opposite: an attribute once included in the table needs to be extracted for a 1:M relationship because the client told you "There's NEVER more than a primary and alternate contact" and that turns out not to be the case after a while, or the business changes.
-- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com
[excessive quoting removed by server]
True... reminded me of the days when I needed to fix the Y2K bugs in a MIS system based on Foxpro/DOS back in year 2000.
Luckily, nothing went wrong! BUT then, there was no change to the database! :)
On Sat, Jun 16, 2018 at 7:20 PM, datades@adam.com.au wrote:
Or an even simpler approach of 'if it aint broke, don't fix it'. Particularly in legacy systems where documentation is out of date or missing and some code does things you don't necessarily understand, just don't touch it.
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....).
Anyway, to illustrate, I had a field called "PTN_factor". This field was only filled in about 30% of the time. However, because of its business value it was included in almost every report and calculation (yes, they simply wanted the confirmation that no PTN_factor was contributing....). So, in that case, I kept the PTN_factor in my "main" table. I too moved the Notes to a separate table - which turned out well because they eventually wanted a "date log" note table (a kind of "baseline", then revisions thing...)
I have seen Oracle queries speed up when the "row width" was significantly reduced. But I have not tested the observation systematically in other RDBMSs. I think there may well be some factor of variable length fields, and needing to parse for the field separator 1,000 times instead of 50. Also, if you frequently do "SELECT *..." I believe a 100 column table significantly takes more server resources (not just temporary space) than a 20 column table (what the server has to do to find and assemble all the column names, etc).
Of course there are all kinds of over variations: 3 columns of containing 4000 characters will give the "feeling" of being slower than 25 integer fields (in regards to user experience, not server resources). So it is sometimes hard to prove/demonstrate.
Thus, my rule of keeping normalization in mind insofar as data that is not top priority to users and/or have a good probability of becoming a 1 to many need. It's still guess work (or scientific analysis if you prefer) <g>
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
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!
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.
What performance changes are you seeing?
Data speed increased to the app, faster save(s) or edits are now all accepted and correct?
Was all data on your remote mySQL data correct? Were you experiencing a longer time to populate FKeys in secondary tables as the #1 obstacle you are overcoming in this data change? If that is the case maybe you needed to look at better loading on the server and less work from the disconnected client.
On Mon, Jun 18, 2018 at 10:17 AM < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
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.
[excessive quoting removed by server]
On 2018-06-18 12:01, Stephen Russell wrote:
What performance changes are you seeing?
Data speed increased to the app, faster save(s) or edits are now all accepted and correct?
Was all data on your remote mySQL data correct? Were you experiencing a longer time to populate FKeys in secondary tables as the #1 obstacle you are overcoming in this data change? If that is the case maybe you needed to look at better loading on the server and less work from the disconnected client.
There was no obvious anything. I changed it for simpler coding maintenance.
Oh I get it. The performance was personal performance. Now you don't have to test all over the place anymore.
Bad __Stephen
On Mon, Jun 18, 2018 at 10:40 PM < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 2018-06-18 12:01, Stephen Russell wrote:
What performance changes are you seeing?
Data speed increased to the app, faster save(s) or edits are now all accepted and correct?
Was all data on your remote mySQL data correct? Were you experiencing a longer time to populate FKeys in secondary tables as the #1 obstacle you are overcoming in this data change? If that is the case maybe you needed to look at better loading on the server and less work from the disconnected client.
There was no obvious anything. I changed it for simpler coding maintenance.
[excessive quoting removed by server]
On 2018-06-19 09:41, Stephen Russell wrote:
Oh I get it. The performance was personal performance. Now you don't have to test all over the place anymore.
Just test to make sure it didn't break anywhere!! This post was more a question of theory, not of an existing issue with this app.
--Good Mike
-
Charlie-gm -
datades＠adam.com.au -
Kurt at VR-FX -
Man-wai Chang -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Stephen Russell -
Ted Roche