DB Strategy

Over time I have created many little web apps that parts of our organization use. Inevitably there's time when I also want to utilize that data in the DB to pull it to the website, often times relating it to another table that may be in another DB. I know there are a variety of different reasons not to put everything in one big database, but how does one relate disparate Datasources? What are the various strategies for this, I can think of some (i.e. DTS duplicating tables) but I wanna know what the big guys are doing. (one quick example of what I am meaning is a zip code DB which I want to be able to relate to a bunch of other tables in various other DBs.) Thanks much, John

Venable, John wrote: > Over time I have created many little web apps that parts of our organization > use. Inevitably there's time when I also want to utilize that data in the DB > to pull it to the website, often times relating it to another table that may > be in another DB. I know there are a variety of different reasons not to put > everything in one big database, but how does one relate disparate > Datasources? What are the various strategies for this, I can think of some > (i.e. DTS duplicating tables) but I wanna know what the big guys are doing. I go for the Big Database, but use different schemas. As long as you use sufficient different database logins, I don't really see the problem with security. Jochem

  You can use ColdFusion's query of a query feature to do this.   A more complex route is to write your own custom code to put two queries together into a new query using the query functions.   However, I do question the comment that you shouldn't put everything in a big database.  Yes, I agree that sometimes there are reasons; such as the data being in no way related.  ( For instance, the web site dedicated to my books vs the web site dedicated to my band ).   If you are talking about related data, I might make the argument that there is no reason to store it in different databases.  Different tables, yes.  Different databases, probably not.   Of course, it depends on your data and usage.  I'm curious (Perhaps even obsessing) over why the data is in different DBs if it needs to be related. At 02:09 PM 2/5/2003 -0500, you wrote: ----- Excess quoted text cut - see Original Post for more -----

That's interesting, I seem to remember a thread on the list a while ago about the best practices for maintenance and security saying that each DB should be fairly narrowly focused. Some of the data is not necessarily related to each other, but more related in that I'd like to be able to do a zip-code statistical look at things in a couple different tables. For instance, I have an Information and Referral database where I need to do zip code lookups. I also have a users database that I would like to do a zip code lookup. Also many of these separate DBs are specific to an application. Oddly enough as I write this i'm seeing way that these things *should* be in the same database. So I guess this turns my question into one of "how do I take many different apps developed over many years and integrate them together..." :-) Let me narrow the focus a bit. Specifically I have a zipcode database that I need to use in various DBs. Is there a "best way" to keep this data current across all instances? Should I replicate it when I install the new one? Thanks, John   You can use ColdFusion's query of a query feature to do this.   A more complex route is to write your own custom code to put two queries together into a new query using the query functions.   However, I do question the comment that you shouldn't put everything in a big database.  Yes, I agree that sometimes there are reasons; such as the data being in no way related.  ( For instance, the web site dedicated to my books vs the web site dedicated to my band ).   If you are talking about related data, I might make the argument that there is no reason to store it in different databases.  Different tables, yes.  Different databases, probably not.   Of course, it depends on your data and usage.  I'm curious (Perhaps even obsessing) over why the data is in different DBs if it needs to be related. At 02:09 PM 2/5/2003 -0500, you wrote: >Over time I have created many little web apps that parts of our organization >use. Inevitably there's time when I also want to utilize that data in the DB >to pull it to the website, often times relating it to another table that may >be in another DB. I know there are a variety of different reasons not to put ----- Excess quoted text cut - see Original Post for more -----

Venable, John wrote: > That's interesting, I seem to remember a thread on the list a while ago > about the best practices for maintenance and security saying that each DB > should be fairly narrowly focused. I would say maybe for security, but not for maintenance. In what scenario do you gain what advantage by using different databases vs. using different logins to one database? > Let me narrow the focus a bit. Specifically I have a zipcode database that I > need to use in various DBs. Is there a "best way" to keep this data current > across all instances? Should I replicate it when I install the new one? Can't you migrate all the different databases into one databases? ;-) Jochem

At 12:49 PM 2/5/2003, you wrote: >Let me narrow the focus a bit. Specifically I have a zipcode database that I >need to use in various DBs. Is there a "best way" to keep this data current >across all instances? Should I replicate it when I install the new one? For something as common as a zip code database I think copying/replicating it into every db you need it in would be more hassle than it's worth. Perhaps I missed it in your earlier post, but if you're using SQL Server (and I'm sure other DB servers can do the same), why not simply reference the foreign db/table in your queries? For example, say you have db/table WebApp/customers and db/table Utility/zipcodes - in your CFQUERY, use the DSN for WebApp and then reference the zip code table as Utility.dbo.zipcodes. Jay

I should like to put my 2¢ in here, since I've often heard the term "database" to be used inappropriately in conversation, even by those persons "working in the field."  In Oracle, you have one Oracle database, typically.  Within this database, you have one or more instances that live wholly on their own, and can only be accessed by one another via dblinks (the "db" starts the confusion).   Within each instance, you have one or more schemas, logically structured to match what it is you're doing. Similarly, MySQL has "databases" that are created, they're not really databases.   They could be likened to Oracle's concept of instances.  They could be connected to one another without a whole lot of difficulty (if I am remembering correctly). I think the key here is that you should have one database (Oracle, SQL Server, MySQL, PostgreSQL, Informix, whatever).  Within this database you can have instances of project data.  This way, the data isn't commingled or lost in a shuffle of hundreds of tables, but theoretically can be accessed from one app to the next. To give you an example, we have in our offices one Oracle database.  That database holds roughly 12+ instances that have little (most times nothing) to do with one another. Hope this helps... Lincoln

Lincoln Milner wrote: > Within this database, you have one or more instances that live wholly on their own, and can only be accessed by one another via dblinks (the "db" starts the confusion). The official name for an instances is "CATALOG" (ISO 9075). Jochem

I am using the term (rightly or wrongly) as SQL Server shows it. I have a single database Server with multiple "databases". I apologize if this is technically incorrect, I am obviously not a database "expert" but these are the terms I was taught and have seen quite often. If it helps, I think what I am referring to as a DB you are calling an instance. john I should like to put my 2¢ in here, since I've often heard the term "database" to be used inappropriately in conversation, even by those persons "working in the field."  In Oracle, you have one Oracle database, typically. Within this database, you have one or more instances that live wholly on their own, and can only be accessed by one another via dblinks (the "db" starts the confusion).  Within each instance, you have one or more schemas, logically structured to match what it is you're doing. Similarly, MySQL has "databases" that are created, they're not really databases.  They could be likened to Oracle's concept of instances.  They could be connected to one another without a whole lot of difficulty (if I am remembering correctly). I think the key here is that you should have one database (Oracle, SQL Server, MySQL, PostgreSQL, Informix, whatever).  Within this database you can have instances of project data.  This way, the data isn't commingled or lost in a shuffle of hundreds of tables, but theoretically can be accessed from one app to the next. To give you an example, we have in our offices one Oracle database.  That database holds roughly 12+ instances that have little (most times nothing) to do with one another. Hope this helps... Lincoln

Is this possible from Cold Fusion? If I could do this it would be the best although I tested it and it didn't work. this query: <cfquery name="qGetRequestsByAffiliate" datasource="inforequests">   SELECT      contacts.date, contacts.firstname, contacts.mi, contacts.lastname, contacts.address1, contacts.address2, contacts.city, contacts.state, contacts.zip, contacts.country, contacts.email, contacts.homephone, contacts.workphone, contacts.age, names.company, contacts.usersmessage   FROM        dbo.contacts, content.dbo.I_aff_names AS names, content.dbo.I_zipcodes AS zips   WHERE       contacts.ZIP = zips.zip AND zips.chapter = names.id AND contacts.date > (getDate() - 7)   ORDER BY company, lastname, firstname </cfquery> throws this error: ODBC Error Code = S0002 (Base table not found) [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'content.dbo.I_aff_names'. BTW, I appreciate everyone's help! John >Perhaps I missed it in your earlier post, but if you're using SQL Server >(and I'm sure other DB servers can do the same), why not simply reference >the foreign db/table in your queries? > >For example, say you have db/table WebApp/customers and db/table >Utility/zipcodes - in your CFQUERY, use the DSN for WebApp and then >reference the zip code table as Utility.dbo.zipcodes.

Hmm, I do this all the time and just verified it works from both CF 4.01 using ODBC and CFMX using the native SQL driver. Does the user/pass used in your "inforequests" DSN have sufficient rights to the "content" db? Jay At 02:20 PM 2/5/2003, you wrote: ----- Excess quoted text cut - see Original Post for more -----

Jay, You are absolutely right and I thank you profusely! I screwed up on the   table name. At any rate, this worked fine for me (once I realized the   err in my ways) using OLEDB drivers on CF 5, haven't tested with ODBC. Thanks so much to all who helped. John Venable On Wednesday, February 5, 2003, at 06:27 PM, Jay Freeman wrote: ----- Excess quoted text cut - see Original Post for more -----

I haven't tried this myself but I think  you need a native driver for this rather than an odbc driver. Can someone else confirm or deny this? In any case, you can test this sql by connecting to your database directly (using sqlplus or other appropriate tool) and running it. --- "Venable, John" <jvenable@efa.org> wrote: ----- Excess quoted text cut - see Original Post for more ----- ===== I-Lin Kuo Macromedia CF5 Advanced Developer Sun Certified Java 2 Programmer __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com