[dba-SQLServer] Managing orders database

JWColby jwcolby at colbyconsulting.com
Thu Apr 19 05:54:25 CDT 2007


I emailed the group a few months ago about strategies for managing the
multitude of queries / views created in the process of filling count orders
for my bulk mail client.  This client will send "orders" for counts of
records, perhaps something like "a count of all the addresses in the
supplied list of zips, where income = X and age between ..." etc.  They then
come back for modifications to the counts - "refining" the count request.
They may come back for "refinements" a half dozen times or more.  Once they
are satisfied with the counts they just "go away", i.e. all activity on that
order ceases.  It is possible however that they may come back weeks or even
months later and ask for more "refinements" to the counts so it is critical
to keep the order information / views pretty much forever.  This process
leaves me with, in some instances, a dozen or two views used to build up the
count requests for each order.  Given that the client may do a handful of
completely different count requests (for different clients of theirs) every
week, these views can proliferate and create a maintenance nightmare. 
 
I discussed with the group solving the problem by creating a new database
for each order.  Doing so isolates all of the views required for any given
order into a database file named to match the order from my client.  These
database files are quite small (about 1-2 megs).  The downside is that now
the number of databases proliferates in my database directory out on disk.  
 
I have implemented this strategy and in fact it is working quite well.  I
can disconnect the database for a given order as activity for that order
ceases, thus it no longer appears in the list of databases inside of
Management studio.  In the event that the client comes back to me to get new
counts for that order I can simply re-attach the database and continue
working in that order database.  Since the order databases are named with
the order number from my client, it is dead simple to find and attach the
order database.  Since each order database has exactly and only the views
required for processing that order, managing the views is vastly simplified.
No longer do I have hundreds of views in a single database attempting to use
a naming convention to keep them straight, and no longer do I have to wade
through views that have nothing to do with the order I am working on.  I
keep the order databases attached until activity ceases, then disconnect
them.
 
"Works fine, lasts a long time with proper maintenance" as we used to say
somewhere in the past.
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 



More information about the dba-SQLServer mailing list