A good approach to db planing for reporting service
- by Itay Moav
The scenario:
Big system (~200 tables).
60,000 users.
Complex reports that will require me to do multiple queries for each report and even those will be complex queries with inner queries all over the place + some processing in PHP.
I have seen an approach, which I am not sure about:
Having one centralized, de-normalized, table that registers any activity in the system which is reportable. This table will hold mostly foreign keys, so she should be fairly compact and fast.
So, for example (My system is a virtual learning management system),
A user enrolls to course, the table stores the user id, date, course id, organization id, activity type (enrollment).
Of course I also store this data in a normalized DB, which the actual application uses.
Pros I see: easy, maintainable queries and code to process data and fast retrieval.
Cons: there is a danger of the de-normalized table to be out of sync with the real DB.
Is this approach worth considering, or (preferably from experience) is total $#%#%t?
