Solved

Best practices for retrieving data from database using coldfusion

Posted on 2014-10-07
6
122 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
  • 3
  • 2
6 Comments
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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 28

Expert Comment

by:Pravin Asar
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 28

Accepted Solution

by:
Pravin Asar earned 500 total points
Comment Utility
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
Comment Utility
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 28

Expert Comment

by:Pravin Asar
Comment Utility
Thanks for the points.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction A frequently asked question goes something like this:  "I am running a long process in the background and I want to alert my client when the process finishes.  How can I send a message to the browser?"  Unfortunately, the short answer …
Introduction If you're like most people, you have occasionally made a typographical error when you're entering information into an online form.  And to your consternation, the browser remembers the error, and offers to autocomplete your future entr…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery including how to code hide show and toggles. 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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now