StrataFrame Forum

SQL Best Practice

http://forum.strataframe.net/Topic26863.aspx

By Larry Caylor - 4/15/2010

In my shop we have been having a lively discussion on where to put in-house developed custom objects that access data in third-party databasesw00t The majority of the time the custom object is a view and all that's needed is read access. One side favors placing all custom objects in a separate database and accessing the target database from the custom objects DB. Their argument is that this approch simplifies the DBA's job and doesn't muck up the thrid-party DB. The other side favors placing the custom objects in the target databse making sure they are named with a prefix so they may be easily identified. Their arguments include minimizing the number of databases that must be managed and improving security since a user can easily be limited to only those objects they require. Maintenance shouldn't be a problem since the custom objects may be easily added or modified by a script that is part of the DBA's maintenance process.

I've been trying to Google something but so far haven't been able to isolate this particular issue. Has anyone else here had this discussion or care to post an opinion?

By Dustin Taylor - 4/16/2010

My personal preference would be to include the views into one of the existing DBs (either yours, or the target DB). But, in reallity, it seems that the issue here isn't database design as much as it is minimizing the communication with and interference from the DBAs. From a technical perspective, I don't see a huge benefit doing it either way. The first solution ensures the existing DBs stay "clean", but requires marginally more work to manage the other DB. The second solution doesn't require another DB, but requires that you be sure and keep the views and existing items seperate.

From our own experience, the only DBAs we deal with are network DBAs in large medical clinics. In that case, we would be better suited to imbed the views in an existing DB since we are usually able to get the DBAs to keep their hands off of our specific databases so long as we don't play outside of our sandbox. One more DB is one more item we have to communicate to the DBAs, and one more opportunity for them to inadvertantly cause issues by removing, moving, locking down, etc. that secondary "views" DB.