Topic: Question re best practice - database views, SQL or other methods?
I've written a system that tracks a customer request from inception to completion. In a nutshell, here's what it looks like:
- A model called case which contains a case ID, case name and other features of the case
- A model called contacts which contains people and locations
- A model called case_contacts which links contacts to a case
- A model called notes which contains notes about the case (fed by other models which feed options into the notes fields)
- A model called case_notes which links notes to a case
My goal is to be able to produce a view (no need to edit the data from this portion of the app) which a clerk can see everything related to the case in some sort of order (e.g. chronological order etc) and I'd also like to be able to filter and display using date entries (i.e. only show me activities in the last 30 days and everything coming up). I gather creating a database view connecting all of these tables together is one way to skin this cat, but it strikes me that this could wind up returning loads of data which may bog the system down as case count, contact and note counts increase.
I think my question is simple - what's the best way to do this? I've read about Database Views and injecting SQL into my app and understand Database views is the best practice, but wanted to take the temperatore of this group before striking out on my own -- I figure this has to be a pretty common function...
Tips and advice are always welcome and thanks in advance.