I am presented with a dilemma while trying to re-designing the class structure for my PHP/MySQL application to make it more elegant and conform it to the SOLID principle.
The problem goes like this:
Let as assume, there is an abstract class called person which has certain properties to define a generic person, such as name, age, date of birth etc.
There are two classes, student, and teacher, that implements this abstract class. They add their own unique properties to it.
I have designed all the three classes to include all the operational logic (details of which are not relevant in context of the question).
Now, I need to create views/reports/data grids which contain details from multiple classes, for example, say, a list of all students doing projects in Chemistry mentored by a teacher whose name is the parameter to the query.
This is just one example of a view, there are many different views in the application, which uses data from 3-4 tables, and each of them have multiple input parameters to generate them.
Considering this particular example, I have written the relevant query using JOIN and the results are as expected and proper, now here is the dilemma:
Keeping in mind the single responsibility principle, where should I keep this query? It does not belong to either Student class, or Teacher class or any other classes currently present.
a) Should I create a new class, say dataView class, and design it as a MVC pattern and keep the query there? What about the other views? how do they fit in this architecture?
b) Should I not keep the query in code at all, and make it DB View ?
c) Am I completely wrong in the approach? If so what is the right approach?
My considerations are as follows:
a) should be easy to add new views later on if requirement comes, without having to copy-paste-modify code
b) would like to make it as loosely coupled as possible so that if minor db structure changes happen, it does not break
I did google searches on report design and OOP report generators, but all the result seem to focus on the visual design of the report rather than fetching the data. I have already taken care of the visual aspect of the report using MVC with html templates.
I am sure this is a very fundamental problem with known solution, but I am somehow not able to find it (maybe searching with wrong keyword).
Edit1: Modified the title to make it more relevant
Edit2: The accepted answer got me thinking in the right direction and identify my design flaws, which eventually led me to find this question and the solution in Stack Overflow which gave me the detailed answer to clear the confusion.
