[dba-SQLServer] Ordering sql server

John:

Here is what I understand you to be asking.  You have a db with static
data.  Client 1 calls with a request for info on that.  You supply it.
Client 1 later calls with a new request.  You supply that.  

You would like to be able to reproduce on demand request 1, 2, etc.
Assuming the underlying data does not change, it means running the query
associated with each request.  To that end, perhaps a table structure
saving the queries (the text of the queries, that is) is the way to go. 

HTH.  D. Lewis 



-----Original Message-----



----------------------------------------------------------------------

Message: 1
Date: Wed, 14 Feb 2007 15:29:16 -0500
From: "JWColby" <jwcolby at colbyconsulting.com>
Subject: [dba-SQLServer] SQl Server Organization
To: <dba-sqlserver at databaseadvisors.com>
Message-ID: <00ac01c75076$ccc95ca0$657aa8c0 at m6805>
Content-Type: text/plain;	charset="us-ascii"

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