Re: OT: Guidelines/Standards for supporting non-oracle

At 02:15 PM 12/10/2002, Rick_Cale_at_teamhealth.com wrote:

>We are virtually an Oracle shop with 2-3 sql server databases due to
>3rd-party software restrictions.
>We have been asked about supporting other "small" databases such as
>Access,etc within our company. My question is if
>you were asked to support "smaller" databases what
>restrictions/guidelines/standards are worth considering?

In addition to conditioning your approval on Mr. Reardon's definition of "small", I'd look at a few things:.

1. Data Integrity - Are these small databases going to contain just read-only snapshots of data in one of your production databases, or are there going to want to manipulate the data. If they say everything's read-only, suggest they can point the Access front-end (or Brio or Crystal Reports) at a schema you'd be happy to set up with appropriate views on the production tables. If they say they'll want to write to the production database, make sure that there are technical and business processes in place to manage this. Make sure they have answers to questions like "what happens when information in Jane's database conflict with information in Tom's and neither agrees with Production?" that aren't "won't happen". At this point, I'd be inclined to say that they're developing non-trivial applications that would probably be better suited to Oracle (or SQL Server).
2. Data cleaning/ validation - Are there any applications out there that clean or validate data in the application, rather than in the database? I suspect there are, and I suspect that getting feeds from other sources that don't include these checks will cause massive problems. I would seriously consider outlawing more than read-only access to systems where you could potentially insert invalid data.
3. Passwords & privs - Access makes it very easy for a user to accidentally overwrite a cell in a grid and modify data. If users know usernames & passwords that have non-read only access to the database, they can potentially get themselves into trouble. Many programmers will grant users more privs than they absolutely need and rely on their applications to only do reasonable things. Once users are logging on via Access, they may do many unreasonable things, often more by ignorance than spite. Managing access to data also becomes much more complicated. It's at least possible to ensure that people don't see data they ought not see in Oracle (or SQL Server). Doing the same in Access is much more difficult. Ensuring that no one has access to data in Access that they wouldn't have in Oracle is very difficult.
4. Data distribution - No manager wants to have data their organization needs being held in dozens of different databases by dozens of other organizations. Often, there's duplication of effort-- often resulting in incompatible solutions-- as no one really knows all the data that's available. Centralizing this sort of thing is a lot easier & less politically painful up front than years down the line when some VP gets upset that the different organizations differ on fundamental things like how products are organized or that they can't easily share information about what software combinations are certified.
5. Lifecycles - What happens when these "small" systems get popular and start growing up. Inevitably, there will be a few that fill a unique niche. These will get more and more users until the "small" database starts to choke. How will this sort of situation be handled? Will the people that know the small database have the skills to recreate it on Oracle (i.e. will they have SQL skills). Perhaps it's better to bite the bullet & teach them SQL to start out. In addition to product lifecycle, also think about data lifecycles. If there's data in Access that isn't captured in one of your production systems, how does that data get identified as sufficiently useful to start incorporating into production systems?

If they're looking at "formalizing" reporting systems (i.e. read-only), that's probably a good thing. You may need to bring on some Access/ Brio/ Crystal expertise either by diving in or bringing in someone experienced. Most mid-sized organizations could probably have a full-time Brio/ Crystal/ Access report writer if they walked around to a few managers and asked them about the reports they're assembling by hand now.

Justin Cave
Distributed Database Consulting

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Justin Cave
  INET: jcave_at_cableone.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).