Re: Schema Inter-dependencies

On 14 nov, 15:52, 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!

It is quite simple: Microsoft still needs to understand the 3-tiered database model: Conceptual/Logical/Physical. Which is what Oracle implemented from the start. Sqlserver is an anomaly.
PLEASE, PLEASE, PLEASE unlearn ALL the EVIL habits you learned from Microsux.
Oracle has SYNONYMS. You don't need VIEWS to make it transparent! And yes, using multiple schemas is normal. Mistaking a schema for a database is not.

So yes, B should be granted access to that particular table, as that is what he needs to see in his 'world'

--
Sybrand Bakker
Senior Oracle DBA


--