?
Solved

Best practices for retrieving data from database using coldfusion

Posted on 2014-10-07
6
Medium Priority
?
142 Views
Last Modified: 2014-10-09
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
Comment
Question by:sscal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 40367502
Can you give us a sample of the actual data (or similar dummy data) and what one of the reports might look like?
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 40367610
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
 
LVL 1

Author Comment

by:sscal
ID: 40368893
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!

 
LVL 29

Accepted Solution

by:
Pravin Asar earned 2000 total points
ID: 40369847
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
 
LVL 1

Author Comment

by:sscal
ID: 40371059
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
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 40372212
Thanks for the points.
0

Featured Post

WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
How to build a simple, quick and effective accordion menu using just 15 lines of jQuery and 2 css classes
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question