artful at rogers.com
artful at rogers.com
Wed Feb 14 15:54:52 CST 2007
You could go that route, JC, but I wouldn't recommend it. I think that you have only one choice, which is to normalize all the columns (the Y/Ns etc.) and to populate the child table with a row for each of these Not-Null (or not Empty) values. Admittedly this will result in a table comprising maybe 500M rows, but it will be searchable by Integer values on a compound index, and thus with a count() and a having() give you the people of interest very quickly. Arthur Fuller Technical Writer, Data Modeler, SQL Sensei Artful Databases Organization www.artfulsoftware.com ----- Original Message ---- From: JWColby <jwcolby at colbyconsulting.com> To: dba-sqlserver at databaseadvisors.com Sent: Wednesday, February 14, 2007 3:29:16 PM Subject: [dba-SQLServer] SQl Server Organization 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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com