 | |
Schema Inter-dependencies
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Schema Inter-dependencies -
11-17-2008
, 07:52 AM
|
On Nov 14, 9:52*am, D-Squared <its.dan.obr... (AT) gmail (DOT) 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 - |
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.
#12
| |||
| |||
|
|
On Nov 14, 9:52*am, D-Squared <its.dan.obr... (AT) gmail (DOT) 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 - |
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.
#13
| |||
| |||
|
|
On Nov 14, 9:52*am, D-Squared <its.dan.obr... (AT) gmail (DOT) 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 - |
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.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.