[dba-SQLServer] SQl Server Organization

JWColby jwcolby at colbyconsulting.com
Wed Feb 14 14:29:16 CST 2007


I'm searching for a strategy for organizing SQL Server (2005 if it makes any
difference).  
 
The situation is that I have a set of tables holding data.  I get "orders"
for counts of data, i.e. how many people where...  The client wants these
counts repeatable, and later to be able to say, "give me records of all the
people represented in that count".  The client also may come back and say
"ok now refine it and give me the same count where..." and the where clause
changes slightly.  Same order, refining the search.
 
I will potentially, sometime in the future, get a half dozen to a dozen of
these orders a week, and each order is completely different from the
previous order, so each requires building new views and queries to create
the counts.  
 
Having done a bunch of these things, I am coming to the conclusion that I
perhaps should create a database for each order, the database named by the
order.  Create all of the views etc required to do the count in its own
database.  That database then pulls the data itself from another database
where the actual data behind the counts resides.
 
Each count does have an "order number" associated with it, so I could of
course prefix any objects created in the process of generating these counts
with the order number.  Thus all the objects would group in the view window
(for example) by Order Number.
 
I could also build a "month" database where all of the objects for all the
orders for a month get placed in that month's database.  
 
The key concept here is that a count order needs to be retained and
repeatable, out how long I do not know but at least until the order is
completed by my client (with his client) and likely even way beyond that.
My client's client may come back and ask for the counts again, or modify the
"where" slightly and ask for the counts again.  Thus I need a system that
groups everything required to get that count, and needs to be easily found
(by the order number).
 
To this point I have been placing all of the objects in the same database
and it is getting ugly.
 
Does anyone have any thoughts on this matter?  
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 



More information about the dba-SQLServer mailing list