• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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?
0
sscal
Asked:
sscal
  • 3
  • 2
1 Solution
 
_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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now