Database design

[m***@mbsoftwaresolutions.com]

(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

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Kurt at VR-FX]

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

Post by m***@mbsoftwaresolutions.com
(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
 - 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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/d702b8bf-65a1-1d79-20de-***@optonline.net
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to

[Stephen Russell]

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 <

Post by m***@mbsoftwaresolutions.com
(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
- 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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYJPc2-RUt=0USRoC6Gm_xuOpRMH49eni2MLJ2LV4=D=***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Man-wai Chang]

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,

Post by m***@mbsoftwaresolutions.com
(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
....
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?

--
.~. Might, Courage, Vision. SINCERITY!
/ v \ 64-bit Fedora 25 Server Spin
/( _ )\ http://sites.google.com/site/changmw
^ ^ May the Force and farces be with you!

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAGv=***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Ted Roche]

Post by Man-wai Chang
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

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CACW6n4sGPLQ79SPqxbeg-mbPh81ycXQHR+EzCNyJ=***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[d***@adam.com.au]

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-***@leafe.com> On Behalf Of Ted Roche
Sent: Saturday, 16 June 2018 7:47 PM
To: ***@leafe.com
Subject: Re: Database design

Post by Man-wai Chang
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/000001d40564$0bff82b0$23fe8810$@adam.com.au
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Man-wai Chang]

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! :)

Post by d***@adam.com.au
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.

--
.~. Might, Courage, Vision. SINCERITY!
/ v \ 64-bit Fedora 25 Server Spin
/( _ )\ http://sites.google.com/site/changmw
^ ^ May the Force and farces be with you!

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAGv=MJDskVLBBqweROV3P6+PjUQuNK8RK8r+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Charlie-gm]

Post by m***@mbsoftwaresolutions.com
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
 - 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




_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/a75db71b-ff7e-b7d7-b923-***@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers wh

[m***@mbsoftwaresolutions.com]

Post by Charlie-gm

Post by m***@mbsoftwaresolutions.com
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
 - 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>

Post by Charlie-gm
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!

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvio

[m***@mbsoftwaresolutions.com]

Post by m***@mbsoftwaresolutions.com

Post by Charlie-gm

Post by m***@mbsoftwaresolutions.com
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
 - 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>

Post by Charlie-gm
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.

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see th

[Stephen Russell]

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 <

Post by m***@mbsoftwaresolutions.com

Post by m***@mbsoftwaresolutions.com

Post by Charlie-gm

Post by m***@mbsoftwaresolutions.com
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
- 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>

Post by Charlie-gm
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYJ=OOkpEtxXRNYwew=aHSXSHg2eieEBJJ=***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[m***@mbsoftwaresolutions.com]

Post by Stephen Russell
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.

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Stephen Russell]

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 <

Post by m***@mbsoftwaresolutions.com

Post by Stephen Russell
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYLu=***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[m***@mbsoftwaresolutions.com]

Post by Stephen Russell
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

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.