As my application has grown, I've noticed that I am reusing a lot of database queries across multiple webpages.
At the moment I have done it using a .CFM file which has many <cfstoredproc>
tags that gets included on every page that needs database data. All I am doing is wrapping these stored procedure executions in a <cfif>
tag which tests what the name of the calling page is and then executes the appropriate <cfstoredproc>
block of code.
I am no expert in anything, but this doesn't feel right to me. I just don't know how to manage all my database queries correctly so that they can be shared across any CFM page in the entire website. For example, one page might need the "GetUsers" stored procedure and another page might need "GetOrders".
I'm just about to embark on creating a CFC which holds every separate <cfstoredproc>
or <cfquery>
in its own method/function. E.g.:
<cfcomponent name="DBQueries" hint="Everything for DB retrieval">
<cffunction name="GetUsers" returntype="query">
<cfstoredproc procedure="GetUsers">
<cfprocresult name="rsUsers">
</cfstoredproc>
<cfreturn rsUsers>
</cffunction>
.....
<cffunction name="DBQuery100">
<cfstoredproc procedure="GetSomething" returntype="query">
<cfprocresult name="rsSomething">
</cfstoredproc>
<cfreturn rsSomething>
</cffunction>
</cfcomponent>
Then on a main .CFM page I will invoke the component and method required to return the data. Is this a good way to achieve DB query management?
Consider the following two db tables
User
UserID PrimaryKey firstname lastname
Security
SecurityID PrimaryKey UserID ForeignKey Permission
All database tables have Create, Read, Update, Delete operations (CRUD)
CRUD operations can exist in several places
<cfquery>
tagsThe thing is all the CRUD operations belong together in their own way. Consider making a User object (
user.cfc
).Security is a part of user management, so is the object a one to one match to the db table? In some environments like ORM the answer is yes, in others not.
If you consider security to be a part of user managment, your
user.cfc
might look like thisAt the end of the day you may find that you need far fewer objects (
*.cfc
s) than tables.OK, now you have you
user.cfc
what do you do with it? It can be attached to you the rest of your app in various different waysEach one of these is very from the next. Before we go down the road of which is appropriate, we have to consider member data, and how long we want it around.
It is likely that your CRUD operations are going to interact with the same
UserID
for all their operations. You may find that after you update a record, you will often read it. Rather than always stating whichUserID
you are interacting with, you may just want to set it up once, and have all the functions just use the same one.OK, now let's get back over to where you will be using them
application.User
Only one
User
object will exist in the the whole system. It will be created when the request comes in onthe site. This object will be shared for every request. If you attach youruser
object here, that suggests that all requests will be looking at the same user.session.User One
User
object will exist for a given end-user in the outside world. It will separated from all other end-users. This suggests that each end user will be looking at their ownuser
AND that even as they click around the site, they will still be looking at the sameuser
request.User One
User
object will exist per request. It will only exist for a particular request, and then be discarded. This suggests that looking at at particularUser
is meaningful on this request, but the next may be quite different, or maybe not even about users.~~~~~~~~~~~~~~~
At the end of the day, you will need to decide how to bundle your DB interactions, and how long you will keep those bundled action together