How would you model this application?

Question

I've got an MVC application written with Zend Framework that pulls data from an Oracle 10g database and displays this data in Tables and Lists and visually enriches this data through colors and charts. There is no ORM and no Create, Update or Delete involved, just pure Reading. The data is inserted from another application. The data in the DB is modeled after the concepts they represent and accessed by DB Views that aggregate this data from various other tables (legacy, can't change), e.g.

| Event ID | Start               | End                 | Status | Created_By |
-----------------------------------------------------------------------------
| 12345678 | 2009-10-01 12:00:00 | 2009-10-01 12:15:00 | booked | John Doe   |
| 12345679 | 2009-11-01 13:00:00 | 2009-12-01 12:00:00 | booked | John Doe   |
| 12345680 | 2009-11-01 13:00:00 | 2009-12-01 12:00:00 | tba    | Jane Doe   |

Users can influence column display, ordering and sorting from the View. Clients can deny/allow access to columns and limit column content to certain values. Users cannot override client settings. A User is an actor, while a client is basically just a filter that creates a subset of available data to a user belonging to the client. User and Client settings are persisted.

My current approach works roughly like this:

Request --> Controller
            | <--> sanitizes and returns Request params
            | ---> Facade (capsules steps to fetch View Data)
            |      | <--> Table Data Gateway builds Query for requested View
            |      | <--> Query Decorator¹ applies User/Client settings
            |      | <--> DB Adapter fetches RecordSet from decorated Query
            | <----returns Recordset
            | <--> applies RecordSet to View
            | <--> Data-Aware ViewHelper render RecordSet (and View)
Response <--returns rendered View

¹ The Query Decorator can read in the persisted User/Client settings and add it to the basic Query object returned by the TDG on the fly.

However, lately I've been doubting this approach and want to improve it. I guess I could remove the TDGs completely and make View building fully generic from the UI; based on the DB structure alone. The users would certainly like this. The thing is, the View has to know a lot about the data. The ViewHelpers have to know column names in order to enrich the data and often they do so in regards to multiple columns in the Recordsets. They cannot be generic and something tells me this is trouble anyway. It feels like mishmash. I just cannot pinpoint why.

Any patterns, ideas - and opinions - are greatly appreciated. I know the question is somewhat vague, but like I said, I cannot pinpoint what makes me doubt this approach. So I guess I am looking for any good practise approaches to building user and client customizable database centric applications in a maintainable way. I certainly don't need a solution, just some ideas and maybe a few links to see how other people approach this problem, so I can take it into account on the next refactoring.

Note
I'll leave the question open for the full duration before accepting an answer. Any input is appreciated.

Answer 1

DB schema design has different requirements (query/write performance, scalability) as a nice-UI (good page-flow, supporting the way people work or how processes work). Quite often therefore the UI and DB approach are difficult to map directly.

As a bad example I remember an application using 'Oracle Forms', which was directly presenting a generic view from the database structure. For non-techie folks it was often very counter-intuitive to use.

Still I think in your case, if the View can be directly mapped to DB schema, sure get rid of unneccessary abstraction-layers, code and simplify the system. Implement the requirements as simple as you can.

Answer 2

After re-reading your question a few times and reflecting on it for a bit, I believe I would sum up the situation thusly:

The "M" is missing from your "MVC".

At this point, you've hand-crafted a relational database schema such that it has a 1:1 mapping with your domain model. And that's great, it makes mapping very easy, but a recordset is still not a domain class.

The term Model in the context of MVC refers to a domain model, not a relational model. If you have a relational database backing this application, then you need some kind of mapping. That's not to say you need a full-fledged ORM framework like Doctrine - although I do find that these tools make my life much easier, even for small projects - but you need something. In fact, the Zend Framework even goes into lengthy detail about mapping a domain model in the Quick Start.

I don't think you need to remove the TDG. Abstractions are good. Ripping it out to make your application a little leaner is something I would compare to going into an office building and ripping out the phone system on the grounds that employees can just use their cell phones. They can, but you don't want them to, same way you don't want your views throwing SQL queries directly at the database. It's inefficient and generally difficult to manage.

My version of the architecture would look like this:

Request --> Controller
            | <--> sanitizes and returns Request params
            | ---> Facade (encapsulates steps to fetch View Data)
            |      | <--> Table Data Gateway builds Query for requested View
            |      | <--> Query Decorator applies User/Client settings
            |      | <--> DB Adapter fetches RecordSet from decorated Query
***         |      | <--> Mapping layer converts RecordSet to Domain Model
***         | <----returns Model
***         | <--> applies Model to View
***         | <--> Data-Aware ViewHelper render Model (and View)
Response <--returns rendered View

I've marked the change lines with ***. Really, the only thing I've changed is that instead of picking up a record set from the façade, it's picking up a model (likely an array of domain classes), and applying that to the View.

Instead of terms like $row['Status'] in your View [Helper], you'll have $event->status, which is safer and simpler to maintain over the long term. No column name in there, just a property.

Now you explicitly mentioned at the very top of your question that you don't have any ORM, so I think you're probably aware of most of this and maybe just needed a push. Those nagging doubts in your mind are probably along the lines of: What if it's not always read-only? What if the data model becomes more complicated? What if people start asking for more complicated reports?

All of these things are the reason why you have a domain model, why it's in fact the fundamental building block of MVC: Eventually, the mental model your users have will fall out of sync with the data model, for a number of reasons which I won't get into here. The point is, it almost always happens.

Am I sure that this is necessary? Am I positive that it's not just overkill, a bunch of ritual incantations that have no meaning for such a small project?

No, I'm not. Only you can decide that. What I can tell you is that the MVC paradigm as a specific architecture isn't doing you much good without a proper domain model. It's a bit better, but not that much better than just having inline queries or façade calls in each page. Without a model, MVC is not much more than a fancy URL-rewriting scheme.

Maybe you need this level of abstraction, maybe you don't; but I'm guessing that you probably suspect you might, otherwise you wouldn't have asked the question. Think about it, analyze the current requirements and scope, ask yourself what kinds of changes are likely, and if the current architecture seems too brittle to accommodate that, then the next logical step would be a domain model - even if today it's just an exact mirror of the relational model. Tomorrow it might not be.

Hope that this is the sort of answer you were looking for!

Answer 3

For getting data dynamically from table based on user input you can use Zend_Dojo components data grid. You can create one TDG object which will remap to new table based on the user input.

http://zendguru.wordpress.com/2009/01/08/dojo-grid-in-zend-framework-creating-nice-and-cool-grid-in-php-using-zend-framework-and-dojo/