Drill down query (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:17
Joined
Feb 19, 2002
Messages
33,677
The Revision table is not 1-1. It is 1-m. The quote table with the static data and the Rev table with the items that change for each rev.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:17
Joined
Oct 29, 2018
Messages
17,113
The Revision table is not 1-1. It is 1-m. The quote table with the static data and the Rev table with the items that change for each rev.
My mistake. I was referring to this image the OP posted regarding this thread (post #6). I thought you were referring to the same thing.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:17
Joined
Feb 19, 2002
Messages
33,677
No, that is what I told him was NOT correct.
 

tmyers

Active member
Local time
Yesterday, 19:17
Joined
Sep 8, 2020
Messages
717
I breaking that into a separate table was still a bad idea? I will recombine it.

Could either of you though possibly answer as to why having them apart like that would be bad? I know you said normalization. I have read some on that, but don't even begin to suggest I understand it.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:17
Joined
Feb 19, 2002
Messages
33,677
There is no reason to isolate those few fields in a 1-1 relationship. From what I can see of the job details table, I don't see any repeating groups. Those would be off loaded to a many side table with one row for each repeating instance rather than one column. Some repeating groups are obvious such as Jan, Feb, Mar, etc. Others such as Heat, Electricity, Cable, etc. are less obvious. Also look for any group of fields with the same name + a numeric suffix such as Child1, Child2, etc.
 

tmyers

Active member
Local time
Yesterday, 19:17
Joined
Sep 8, 2020
Messages
717
There is no reason to isolate those few fields in a 1-1 relationship. From what I can see of the job details table, I don't see any repeating groups. Those would be off loaded to a many side table with one row for each repeating instance rather than one column. Some repeating groups are obvious such as Jan, Feb, Mar, etc. Others such as Heat, Electricity, Cable, etc. are less obvious. Also look for any group of fields with the same name + a numeric suffix such as Child1, Child2, etc.
I see. We crossed a little bit as I added a little more to my previous post.

What is the downside of separating the two? You said normalization, which I get even though I don't even remotely fully understand normalization myself. Is it just chalked up to unnecessary and wasted space?

Edit:
Also I apologize if I seem like I am coming off as stubborn and unwilling to listen. That is certainly not my intent.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:17
Joined
Feb 19, 2002
Messages
33,677
The downside is having two tables when you should logically have only one. That means that you will always have to join the two tables since they do not stand on their own. I'm sure your table sizes are not large enough for this to be a performance issue. It is simply a logic issue and will disturb anyone who knows anything about designing a relational database. A lot of the stuff on normalization is written in gibberish. Try to find sites that don't use relational algebra to explain the concepts. They can be explained in English which is less precise but much easier to understand. If you don't understand the explanation on one site, just look for another. Relational database design is all about doing the right thing. These rules were formulated over 50 years ago and have stood the test of time. During that time, database engines have gotten very sophisticated in how they manage data to support efficient retrieval. The SQL language has evolved somewhat to incorporate new features but the core of the system is still rock solid. I'm going to suggest you just go with the "best practice" recommendations unless you know something I don't know.
 

Users who are viewing this thread

Top Bottom