Re: Schema Inter-dependencies

On Nov 15, 3:33 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Nov 14, 9:52 am, D-Squared <its.dan.obr..._at_gmail.com> wrote:
>
>
>
>
>
> > I am at a new position where they host multiple applications in Oracle
> > (9.2.07, just migrated to 10gR2) that are all hosted on the same
> > database.  The applications have references across schemas, nothing
> > special there.  My question, coming from a SQL Server environment, is
> > what is best practices for this type of environment?  I understand if
> > multiple schemas have interdependencies if they are all a part of the
> > same external schema.  However, I question what the best practice is
> > if I have multiple apps, (A, B, C....Z) and B has external
> > dependencies on A's schema objects.  Is this considered best
> > practice?  Or would it be better to have each app (and associated
> > schemas) reference each other through views?  For example, for the B
> > app and B schema to get to the A apps A schema, would it be better to
> > create view B.V_EXTERNAL_1 which selects out of A.V_EXTERNAL_1?  This
> > way if the schema in A or B changed I would not have to rewrite
> > application code or SP's.
>
> > Also, what do I do if A is a user tracking application that holds a
> > distinct ID of all users in the enterprise.  B needs to validate the
> > email and contact info of a user in the enterprise. Should B, a
> > completely seperate app living on the same database, directly
> > reference A?
>
> > Any help you could provide would be greatly appreciated!
>
> The only real issue with multiple owners is the need to grant
> References from one owner to another if the tables are related and FK
> constraints are defined to manage the data relationship.  Grants to
> the users are handled via roles.  Where the tables belong to a
> specific application and there is very little data overlap to other
> applicaiton tables stored in the database having a separate owner can
> assist management of the application.
>
> For most, but not all, of our home grown applications we use one owner
> while we retain a separte owner for each of our vendor supplied
> products.
>
> Also Sybrand had a point.  Oracle and SQL Server are not designed the
> same way and do not work the same way.  Code developed on one may not
> work as expected on the other usually due to the differences in the
> read consistency models.  Make sure, if you are going to be working on
> both, that you learn and develop an understanding of those
> differences.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

Thanks for the input. I have worked in both systems and understand the fundamental differences in their models. My main issue is that in previous positions I was working for ISV's where we did not have to answer these questions since it was all one model. I appreciate the input. Received on Mon Nov 17 2008 - 07:52:13 CST