DB Design suggestions
-4
I am looking for most optimal DB Solution for the below case.
Consider that I have a Parent Table [P1] which has columns [ ID | StudentID | StudentName ], and multiple child tables derived from P1, lets say C1, C2, C3 ... [ This grows ] where every Table's composite primary key is [StudentID , ReportingDate] and other factual data respective to the table.
I have a use case where if given StudentID, I need a list of records from all the tables of the given key in the format [ Table Name | StudentID | ReportingDate ].
Sample Input:
Search for STD1
Sample Output:
Table Name| Searched Key | ReportingDate
C1 | STD1 | Date1
C3 | STD1 | Date1
C1 | STD1 | Date2
Child tables are different Reports generated based out of StudentID's on different dates and that will be growing.
I can't make joins of growing Child tables. Lets say I have 30 - 40 Child tables.
Do we have some solution for this?
sql database-design relational-database
edited Nov 12 at 15:20
a_horse_with_no_name
291k46444538
asked Nov 12 at 14:20
Sai
12
add a comment |
-4
I am looking for most optimal DB Solution for the below case.
Consider that I have a Parent Table [P1] which has columns [ ID | StudentID | StudentName ], and multiple child tables derived from P1, lets say C1, C2, C3 ... [ This grows ] where every Table's composite primary key is [StudentID , ReportingDate] and other factual data respective to the table.
I have a use case where if given StudentID, I need a list of records from all the tables of the given key in the format [ Table Name | StudentID | ReportingDate ].
Sample Input:
Search for STD1
Sample Output:
Table Name| Searched Key | ReportingDate
C1 | STD1 | Date1
C3 | STD1 | Date1
C1 | STD1 | Date2
Child tables are different Reports generated based out of StudentID's on different dates and that will be growing.
I can't make joins of growing Child tables. Lets say I have 30 - 40 Child tables.
Do we have some solution for this?
sql database-design relational-database
edited Nov 12 at 15:20
a_horse_with_no_name
291k46444538
asked Nov 12 at 14:20
Sai
12
1
No we don't have a solution for this. You need to put some effort into it yourself before anyone is going to help. Show what you've tried. Paste your code into the question, and explain what doesn't work.
– rory.ap
Nov 12 at 14:25
Okay .. I should have provided you with more context. It is not that i haven't tried. I am not a expert in DB Design .
– Sai
Nov 12 at 15:01
The immediate thought that came to me is to maintain as separate Table to track the new Child Tables . However i will end up in iteration of all the table and making more joins , which is a costly operation i believe, Which i mentioned as "i cant make Joins " . Moreover, i am in design phase, Hence i don't have any code samples. Additionally, this is not an debugging question. I am looking for expert suggestion to tackle the situation which i may not be aware of and i am eager to know new Designs. If this is not possible i may need to revisit the complete back end design. Thanks !
– Sai
Nov 12 at 15:09
My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". meta.stackexchange.com/q/204461
– philipxy
Nov 12 at 21:30
Please clarify via post edits, not comments. PS What is this notion that joins are problematic? Make straightforward designs using basic principles. (Joins being problematic is not one of them.) You could start learning about query optimization by reading an academic textbook. PS If you are in the design phase then give some correct design. Use DDL--even if you also give a diagram. But since you are also talking about a particular query on a particular design you can & should give a Minimal, Complete, and Verifiable example. But a design must meet a problem specification--what is yours?
– philipxy
Nov 12 at 21:37
add a comment |
-4
-4
-4
I am looking for most optimal DB Solution for the below case.
Consider that I have a Parent Table [P1] which has columns [ ID | StudentID | StudentName ], and multiple child tables derived from P1, lets say C1, C2, C3 ... [ This grows ] where every Table's composite primary key is [StudentID , ReportingDate] and other factual data respective to the table.
I have a use case where if given StudentID, I need a list of records from all the tables of the given key in the format [ Table Name | StudentID | ReportingDate ].
Sample Input:
Search for STD1
Sample Output:
Table Name| Searched Key | ReportingDate
C1 | STD1 | Date1
C3 | STD1 | Date1
C1 | STD1 | Date2
Child tables are different Reports generated based out of StudentID's on different dates and that will be growing.
I can't make joins of growing Child tables. Lets say I have 30 - 40 Child tables.
Do we have some solution for this?
sql database-design relational-database
edited Nov 12 at 15:20
a_horse_with_no_name
291k46444538
asked Nov 12 at 14:20
Sai
12
I am looking for most optimal DB Solution for the below case.
Consider that I have a Parent Table [P1] which has columns [ ID | StudentID | StudentName ], and multiple child tables derived from P1, lets say C1, C2, C3 ... [ This grows ] where every Table's composite primary key is [StudentID , ReportingDate] and other factual data respective to the table.
I have a use case where if given StudentID, I need a list of records from all the tables of the given key in the format [ Table Name | StudentID | ReportingDate ].
Sample Input:
Search for STD1
Sample Output:
Table Name| Searched Key | ReportingDate
C1 | STD1 | Date1
C3 | STD1 | Date1
C1 | STD1 | Date2
Child tables are different Reports generated based out of StudentID's on different dates and that will be growing.
I can't make joins of growing Child tables. Lets say I have 30 - 40 Child tables.
Do we have some solution for this?
sql database-design relational-database
sql database-design relational-database
edited Nov 12 at 15:20
a_horse_with_no_name
291k46444538
asked Nov 12 at 14:20
Sai
12
edited Nov 12 at 15:20
a_horse_with_no_name
291k46444538
asked Nov 12 at 14:20
Sai
12
edited Nov 12 at 15:20
a_horse_with_no_name
291k46444538
edited Nov 12 at 15:20
a_horse_with_no_name
291k46444538
edited Nov 12 at 15:20
a_horse_with_no_name
291k46444538
291k46444538
asked Nov 12 at 14:20
Sai
12
asked Nov 12 at 14:20
Sai
12
asked Nov 12 at 14:20
Sai
12
12
1
No we don't have a solution for this. You need to put some effort into it yourself before anyone is going to help. Show what you've tried. Paste your code into the question, and explain what doesn't work.
– rory.ap
Nov 12 at 14:25
Okay .. I should have provided you with more context. It is not that i haven't tried. I am not a expert in DB Design .
– Sai
Nov 12 at 15:01
The immediate thought that came to me is to maintain as separate Table to track the new Child Tables . However i will end up in iteration of all the table and making more joins , which is a costly operation i believe, Which i mentioned as "i cant make Joins " . Moreover, i am in design phase, Hence i don't have any code samples. Additionally, this is not an debugging question. I am looking for expert suggestion to tackle the situation which i may not be aware of and i am eager to know new Designs. If this is not possible i may need to revisit the complete back end design. Thanks !
– Sai
Nov 12 at 15:09
My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". meta.stackexchange.com/q/204461
– philipxy
Nov 12 at 21:30
Please clarify via post edits, not comments. PS What is this notion that joins are problematic? Make straightforward designs using basic principles. (Joins being problematic is not one of them.) You could start learning about query optimization by reading an academic textbook. PS If you are in the design phase then give some correct design. Use DDL--even if you also give a diagram. But since you are also talking about a particular query on a particular design you can & should give a Minimal, Complete, and Verifiable example. But a design must meet a problem specification--what is yours?
– philipxy
Nov 12 at 21:37
add a comment |
1
No we don't have a solution for this. You need to put some effort into it yourself before anyone is going to help. Show what you've tried. Paste your code into the question, and explain what doesn't work.
– rory.ap
Nov 12 at 14:25
Okay .. I should have provided you with more context. It is not that i haven't tried. I am not a expert in DB Design .
– Sai
Nov 12 at 15:01
The immediate thought that came to me is to maintain as separate Table to track the new Child Tables . However i will end up in iteration of all the table and making more joins , which is a costly operation i believe, Which i mentioned as "i cant make Joins " . Moreover, i am in design phase, Hence i don't have any code samples. Additionally, this is not an debugging question. I am looking for expert suggestion to tackle the situation which i may not be aware of and i am eager to know new Designs. If this is not possible i may need to revisit the complete back end design. Thanks !
– Sai
Nov 12 at 15:09
My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". meta.stackexchange.com/q/204461
– philipxy
Nov 12 at 21:30
Please clarify via post edits, not comments. PS What is this notion that joins are problematic? Make straightforward designs using basic principles. (Joins being problematic is not one of them.) You could start learning about query optimization by reading an academic textbook. PS If you are in the design phase then give some correct design. Use DDL--even if you also give a diagram. But since you are also talking about a particular query on a particular design you can & should give a Minimal, Complete, and Verifiable example. But a design must meet a problem specification--what is yours?
– philipxy
Nov 12 at 21:37
1
1
No we don't have a solution for this. You need to put some effort into it yourself before anyone is going to help. Show what you've tried. Paste your code into the question, and explain what doesn't work.
– rory.ap
Nov 12 at 14:25
No we don't have a solution for this. You need to put some effort into it yourself before anyone is going to help. Show what you've tried. Paste your code into the question, and explain what doesn't work.
– rory.ap
Nov 12 at 14:25
Okay .. I should have provided you with more context. It is not that i haven't tried. I am not a expert in DB Design .
– Sai
Nov 12 at 15:01
Okay .. I should have provided you with more context. It is not that i haven't tried. I am not a expert in DB Design .
– Sai
Nov 12 at 15:01
The immediate thought that came to me is to maintain as separate Table to track the new Child Tables . However i will end up in iteration of all the table and making more joins , which is a costly operation i believe, Which i mentioned as "i cant make Joins " . Moreover, i am in design phase, Hence i don't have any code samples. Additionally, this is not an debugging question. I am looking for expert suggestion to tackle the situation which i may not be aware of and i am eager to know new Designs. If this is not possible i may need to revisit the complete back end design. Thanks !
– Sai
Nov 12 at 15:09
The immediate thought that came to me is to maintain as separate Table to track the new Child Tables . However i will end up in iteration of all the table and making more joins , which is a costly operation i believe, Which i mentioned as "i cant make Joins " . Moreover, i am in design phase, Hence i don't have any code samples. Additionally, this is not an debugging question. I am looking for expert suggestion to tackle the situation which i may not be aware of and i am eager to know new Designs. If this is not possible i may need to revisit the complete back end design. Thanks !
– Sai
Nov 12 at 15:09
My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". meta.stackexchange.com/q/204461
– philipxy
Nov 12 at 21:30
My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". meta.stackexchange.com/q/204461
– philipxy
Nov 12 at 21:30
Please clarify via post edits, not comments. PS What is this notion that joins are problematic? Make straightforward designs using basic principles. (Joins being problematic is not one of them.) You could start learning about query optimization by reading an academic textbook. PS If you are in the design phase then give some correct design. Use DDL--even if you also give a diagram. But since you are also talking about a particular query on a particular design you can & should give a Minimal, Complete, and Verifiable example. But a design must meet a problem specification--what is yours?
– philipxy
Nov 12 at 21:37
Please clarify via post edits, not comments. PS What is this notion that joins are problematic? Make straightforward designs using basic principles. (Joins being problematic is not one of them.) You could start learning about query optimization by reading an academic textbook. PS If you are in the design phase then give some correct design. Use DDL--even if you also give a diagram. But since you are also talking about a particular query on a particular design you can & should give a Minimal, Complete, and Verifiable example. But a design must meet a problem specification--what is yours?
– philipxy
Nov 12 at 21:37
add a comment |
1 Answer
1
active
oldest
votes
0
More data must not lead to more tables, but to more rows in a database.
That said, you should probably have one student and one student_report table. Then add some column(s) to the student_report_table, so as to be able to reflect the different reports. In its simplest form that would be a sole report type:
- student (student_id, name, ...)
- report_type (report_type_id, description)
- student_report (student_id, report_date, report_type_id, ...)
answered Nov 12 at 15:31
Thorsten Kettner
50.2k22542
Thanks for the input . I have tried this solution. The problem i faced here is that every report type will have its own columns. In my example, C1 will have [StudentID , ReportingDate] but, we can have 3 factual Data. Similarly, C2 may have 5 Factual data. This was the primary reason for me to go with report wise tables.
– Sai
Nov 12 at 16:40
I have no idea what this "factual data" may look like. In order to help you, we need more details. Please show some sample reports. What is it actually you want to store? The parameters used for the report? Report lines? A report layout? Pictures? PDF? XML?
– Thorsten Kettner
Nov 12 at 17:41
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53264124%2fdb-design-suggestions%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
More data must not lead to more tables, but to more rows in a database.
That said, you should probably have one student and one student_report table. Then add some column(s) to the student_report_table, so as to be able to reflect the different reports. In its simplest form that would be a sole report type:
- student (student_id, name, ...)
- report_type (report_type_id, description)
- student_report (student_id, report_date, report_type_id, ...)
answered Nov 12 at 15:31
Thorsten Kettner
50.2k22542
Thanks for the input . I have tried this solution. The problem i faced here is that every report type will have its own columns. In my example, C1 will have [StudentID , ReportingDate] but, we can have 3 factual Data. Similarly, C2 may have 5 Factual data. This was the primary reason for me to go with report wise tables.
– Sai
Nov 12 at 16:40
I have no idea what this "factual data" may look like. In order to help you, we need more details. Please show some sample reports. What is it actually you want to store? The parameters used for the report? Report lines? A report layout? Pictures? PDF? XML?
– Thorsten Kettner
Nov 12 at 17:41
add a comment |
0
More data must not lead to more tables, but to more rows in a database.
That said, you should probably have one student and one student_report table. Then add some column(s) to the student_report_table, so as to be able to reflect the different reports. In its simplest form that would be a sole report type:
- student (student_id, name, ...)
- report_type (report_type_id, description)
- student_report (student_id, report_date, report_type_id, ...)
answered Nov 12 at 15:31
Thorsten Kettner
50.2k22542
Thanks for the input . I have tried this solution. The problem i faced here is that every report type will have its own columns. In my example, C1 will have [StudentID , ReportingDate] but, we can have 3 factual Data. Similarly, C2 may have 5 Factual data. This was the primary reason for me to go with report wise tables.
– Sai
Nov 12 at 16:40
I have no idea what this "factual data" may look like. In order to help you, we need more details. Please show some sample reports. What is it actually you want to store? The parameters used for the report? Report lines? A report layout? Pictures? PDF? XML?
– Thorsten Kettner
Nov 12 at 17:41
add a comment |
0
0
0
More data must not lead to more tables, but to more rows in a database.
That said, you should probably have one student and one student_report table. Then add some column(s) to the student_report_table, so as to be able to reflect the different reports. In its simplest form that would be a sole report type:
- student (student_id, name, ...)
- report_type (report_type_id, description)
- student_report (student_id, report_date, report_type_id, ...)
answered Nov 12 at 15:31
Thorsten Kettner
50.2k22542
More data must not lead to more tables, but to more rows in a database.
That said, you should probably have one student and one student_report table. Then add some column(s) to the student_report_table, so as to be able to reflect the different reports. In its simplest form that would be a sole report type:
- student (student_id, name, ...)
- report_type (report_type_id, description)
- student_report (student_id, report_date, report_type_id, ...)
answered Nov 12 at 15:31
Thorsten Kettner
50.2k22542
answered Nov 12 at 15:31
Thorsten Kettner
50.2k22542
answered Nov 12 at 15:31
Thorsten Kettner
50.2k22542
answered Nov 12 at 15:31
Thorsten Kettner
50.2k22542
50.2k22542
Thanks for the input . I have tried this solution. The problem i faced here is that every report type will have its own columns. In my example, C1 will have [StudentID , ReportingDate] but, we can have 3 factual Data. Similarly, C2 may have 5 Factual data. This was the primary reason for me to go with report wise tables.
– Sai
Nov 12 at 16:40
I have no idea what this "factual data" may look like. In order to help you, we need more details. Please show some sample reports. What is it actually you want to store? The parameters used for the report? Report lines? A report layout? Pictures? PDF? XML?
– Thorsten Kettner
Nov 12 at 17:41
add a comment |
Thanks for the input . I have tried this solution. The problem i faced here is that every report type will have its own columns. In my example, C1 will have [StudentID , ReportingDate] but, we can have 3 factual Data. Similarly, C2 may have 5 Factual data. This was the primary reason for me to go with report wise tables.
– Sai
Nov 12 at 16:40
I have no idea what this "factual data" may look like. In order to help you, we need more details. Please show some sample reports. What is it actually you want to store? The parameters used for the report? Report lines? A report layout? Pictures? PDF? XML?
– Thorsten Kettner
Nov 12 at 17:41
Thanks for the input . I have tried this solution. The problem i faced here is that every report type will have its own columns. In my example, C1 will have [StudentID , ReportingDate] but, we can have 3 factual Data. Similarly, C2 may have 5 Factual data. This was the primary reason for me to go with report wise tables.
– Sai
Nov 12 at 16:40
Thanks for the input . I have tried this solution. The problem i faced here is that every report type will have its own columns. In my example, C1 will have [StudentID , ReportingDate] but, we can have 3 factual Data. Similarly, C2 may have 5 Factual data. This was the primary reason for me to go with report wise tables.
– Sai
Nov 12 at 16:40
I have no idea what this "factual data" may look like. In order to help you, we need more details. Please show some sample reports. What is it actually you want to store? The parameters used for the report? Report lines? A report layout? Pictures? PDF? XML?
– Thorsten Kettner
Nov 12 at 17:41
I have no idea what this "factual data" may look like. In order to help you, we need more details. Please show some sample reports. What is it actually you want to store? The parameters used for the report? Report lines? A report layout? Pictures? PDF? XML?
– Thorsten Kettner
Nov 12 at 17:41
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53264124%2fdb-design-suggestions%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
No we don't have a solution for this. You need to put some effort into it yourself before anyone is going to help. Show what you've tried. Paste your code into the question, and explain what doesn't work.
– rory.ap
Nov 12 at 14:25
Okay .. I should have provided you with more context. It is not that i haven't tried. I am not a expert in DB Design .
– Sai
Nov 12 at 15:01
The immediate thought that came to me is to maintain as separate Table to track the new Child Tables . However i will end up in iteration of all the table and making more joins , which is a costly operation i believe, Which i mentioned as "i cant make Joins " . Moreover, i am in design phase, Hence i don't have any code samples. Additionally, this is not an debugging question. I am looking for expert suggestion to tackle the situation which i may not be aware of and i am eager to know new Designs. If this is not possible i may need to revisit the complete back end design. Thanks !
– Sai
Nov 12 at 15:09
My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". meta.stackexchange.com/q/204461
– philipxy
Nov 12 at 21:30
Please clarify via post edits, not comments. PS What is this notion that joins are problematic? Make straightforward designs using basic principles. (Joins being problematic is not one of them.) You could start learning about query optimization by reading an academic textbook. PS If you are in the design phase then give some correct design. Use DDL--even if you also give a diagram. But since you are also talking about a particular query on a particular design you can & should give a Minimal, Complete, and Verifiable example. But a design must meet a problem specification--what is yours?
– philipxy
Nov 12 at 21:37