Greg Worthey
greg at worthey.com
Thu Feb 15 13:27:16 CST 2007
John, If I understand you correctly, here's what you need to do: Figure out a set of criteria that describe a "count order", then put each order as a record in a table. You say all the orders are different, but after you get enough of them, they all should fit some set of criteria. Then, when there is some demand against that order (give summary of counts, or give all names, etc), you have a SQL query builder that uses the criteria from the order to query the data on demand. It sounds like you're replicating data where all you need to do is query it. If you create a database for each order, you'll have a huge mess on your hands when they start coming in frequently. Generalize the criteria for the orders and keep everything in one database, and it should be tidy. Greg Worthey Worthey Solutions www.worthey.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of dba-sqlserver-request at databaseadvisors.com Sent: Thursday, February 15, 2007 10:00 AM To: dba-sqlserver at databaseadvisors.com Subject: dba-SQLServer Digest, Vol 48, Issue 11 Send dba-SQLServer mailing list submissions to dba-sqlserver at databaseadvisors.com To subscribe or unsubscribe via the World Wide Web, visit http://databaseadvisors.com/mailman/listinfo/dba-sqlserver or, via email, send a message with subject or body 'help' to dba-sqlserver-request at databaseadvisors.com You can reach the person managing the list at dba-sqlserver-owner at databaseadvisors.com When replying, please edit your Subject line so it is more specific than "Re: Contents of dba-SQLServer digest..." Today's Topics: 1. SQl Server Organization (JWColby) 2. Re: SQl Server Organization (JWColby) 3. Re: SQl Server Organization (artful at rogers.com) 4. Re: SQl Server Organization (JWColby) ---------------------------------------------------------------------- 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 ------------------------------ Message: 2 Date: Wed, 14 Feb 2007 15:53:53 -0500 From: "JWColby" <jwcolby at colbyconsulting.com> Subject: Re: [dba-SQLServer] SQl Server Organization To: <dba-sqlserver at databaseadvisors.com> Message-ID: <00b401c7507a$3d092470$657aa8c0 at m6805> Content-Type: text/plain; charset="us-ascii" On a related note, what makes queries so special that they get stored out on the hard disk as opposed to in a tab in the database? John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Wednesday, February 14, 2007 3:29 PM To: dba-sqlserver at databaseadvisors.com 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 ------------------------------ Message: 3 Date: Wed, 14 Feb 2007 13:54:52 -0800 (PST) From: artful at rogers.com Subject: Re: [dba-SQLServer] SQl Server Organization To: dba-sqlserver at databaseadvisors.com Message-ID: <311573.24073.qm at web88211.mail.re2.yahoo.com> Content-Type: text/plain; charset=iso-8859-1 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 ------------------------------ Message: 4 Date: Wed, 14 Feb 2007 18:56:30 -0500 From: "JWColby" <jwcolby at colbyconsulting.com> Subject: Re: [dba-SQLServer] SQl Server Organization To: <dba-sqlserver at databaseadvisors.com> Message-ID: <00c001c75093$bfb3e180$657aa8c0 at m6805> Content-Type: text/plain; charset="us-ascii" Even should I go that route I still need to be able to save whatever queries, views and other objects are used to obtain a count. And I don't have a system in place to do what you mention so there is "the meantime" to deal with. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com Sent: Wednesday, February 14, 2007 4:55 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQl Server Organization 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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com ------------------------------ _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver End of dba-SQLServer Digest, Vol 48, Issue 11 *********************************************