Solved

Best practices for retrieving data from database using coldfusion

Posted on 2014-10-07
6
140 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
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!

 
LVL 29

Accepted Solution

by:
Pravin Asar earned 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
In this article you'll learn how to use Ajax calls within your CodeIgniter application. To explain this, I'll illustrate how to implement a simple contact form to allow visitors to send you an email through your web site.
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)

726 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