Best practices for retrieving data from database using coldfusion

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?
LVL 1
sscalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Can you give us a sample of the actual data (or similar dummy data) and what one of the reports might look like?
0
Pravin AsarPrincipal Systems EngineerCommented:
From the description of the problem, looks like you  need to have configurable report builder, which can
1.  permits  user to select columns to be included in the report.
2. Maybe allow user to decide column order.

Based on the schema you described, looks like you have to do join on categoryid and entityid fields from table2 and table 1.

I agree,  selecting all columns may be very inefficient.


Provide more details.
0
sscalAuthor Commented:
Thanks for your input.  Below is some sample data for the tables and I have attached a mockup of a sample report.

For some clarification, the reports are predefined reports, so the ability to select columns for inclusion and sorting by the user is not immediately necessary (would be a nice enhancement down the line).

Table 1 (entities):
entityID        entity
1       entity1
2       entity2
3       entity3
4       entity 5

Table 2 (categories)
categoryID        Category
1       cert
2       class
3       exp
4       rev
5       fac
6       serv

Table 3 (data):
dataID        EntityID        yr        categoryID        amount
1      1      1213      1      1000
2      1      1213      2      1500
3      1      1213      3      6000
4      1      1213      4      5500
5      1      1213      5      1500
6      2      1213      1      2000
7      2      1213      2      1500
8      2      1213      3      7500
9      2      1213      4      6500
10      2      1213      6      1500
11      3      1213      1      750
12      3      1213      2      1250
13      3      1213      3      3500
14      3      1213      4      3500
15      3      1213      5      1500

Report/Output sample
Rank      Entity      cert      class      exp
1      Entity 2      2000      1500      7500
2      Entity 1      1000      1500      6000
3      Entity 3      750      1250      3500
ee-sample-rpt.pdf
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Pravin AsarPrincipal Systems EngineerCommented:
Thanks for posting more details.

Since you said, you have pre-canned reports (meaning you know, which columns and their order), I suggest you can define
a table  that will keep  name of reports and associated query string
(e.g.
tblReports
ReportID, ReportName, ReportQueryStr, ColumnOrder

1,
'Sample Report1' ,       
'select data.dataid, data.amount, data.year, category.category, entity.entity from data,  category, entity where (category.categoryid = data.categoryid)  and (entity.entityid = data.entityid);',
 'dataid, entity, category, amount, year'

So once you define your SQL string and column order you can build generic report writer.
The report writer would
   1 Pull a record from tblReports for selected report id/name.
   2. Run sql query for the retrieved record  from tblReports
   3 Parse the column names in from Columnorder'
   4 Spit out the report by looping over the  column names (obtained from ColumnOrder) and data resultset (obtained by running the query).

for example
<cfoutput query="data">
      <cfloop list="dataid,amount,year" index="col">
      #data[col][currentRow]#
</cfloop>
</cfoutput>

Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sscalAuthor Commented:
Thanks for your time and thoughts.  I had a hunch query the data and looping through the column list would be the most efficient method.  I wasn't sure if there was some other method (retrieving data as JSON, converting query to array or something else) that would be more efficient.

Sometimes the simplest solution is the best solution.
0
Pravin AsarPrincipal Systems EngineerCommented:
Thanks for the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.