I am looking for some suggestions on the most effective way to retrieve and display data from a table. For background, the data was in a column heavy table format, with more than 150 distinct columns (all values are numeric). I am moving the reporting tool to our website, which is uses Coldfusion 10, MySQL and JQuery, am trying to make it a bit more efficient to query as well as maintain.
I have migrated the table to three new tables in the following format
Table 1 (entities):
entityID | entity
Table 2 (categories):
categoryID | Category
Table 3 (data):
dataID | EntityID | yr | categoryID | amount
My challenge is that I need to output the data along columns (4-6 to a report). We have about three dozen different reports using the data, so whatever solution I come up with needs to be flexible.
Any suggestions or thoughts?