This discussion is archived
3 Replies Latest reply: Mar 4, 2013 1:37 AM by APC RSS

New database or new schema?

Napster Newbie
Currently Being Moderated
Hi Guys,

I am not sure if I am posting my question in the correct forum, and if not please bare with me.

I am in the process of adding a new extension to an existing application. Programming language of the front end of the two applications are different but they will require to use common set of basic data stored in an oracle database. Existing application has a set of database objects assigned to a set of sachems. My new module will also have several database objects (tables, views, ect...) and these objects has to be assigned to a new schema. However same set of users accounts should be allowed to access both applications.

I was wondering what would be the proper way to implement this new module...

First option is to create a completely new database and a schema for the new module in the same server and implement my new application extension there... So our client will have set of duplicated Dev, Test and Prod databases in their server ultimately.

Other option is to create a new schema in the exiting database instances for the objects in my new module.

What would be the best option to implement this? Is there other ways to create my extension in the same server?

Any help is very much appreciated...
/Napster
  • 1. Re: New database or new schema?
    BluShadow Guru Moderator
    Currently Being Moderated
    Why go to the trouble of creating a whole new database and duplicating your data? You would be better just creating a new schema for the new application and applying appropriate grants so users have the access to the data they need.
    However same set of users accounts should be allowed to access both applications.
    Are these database users or some sort of application/business users that are stored as data in the tables etc. Too often people talk about 'users' of applications but are not really talking of actual database users, so you need to be clear of the details when explaining.
  • 2. Re: New database or new schema?
    Napster Newbie
    Currently Being Moderated
    Hi BluShadow,

    Thanks for the reply... I am referring to the oracle users... Well they are same as the application users in my case. And regarding the creating new the databases for the extension, here I will only be adding the new objects that are needed for the extension to the new database. So in this case the extension will be connected to both the original database and the new database.

    Any recommendations? And any issues that I could face if I choose to create a new schema.

    Thanks...
  • 3. Re: New database or new schema?
    APC Oracle ACE
    Currently Being Moderated
    Napster wrote:
    Any recommendations?
    This is a design issue, and as such a matter of personal taste.

    Personally I am a great fan of schemas, and would always plump for a s separate schema for every sub-system. It is a useful way of defining application code and data, and promotes clear interfaces between sub-systems. In a large organisation, subsystems often represent different parts of the business and so have different owners; hanece the need for clarity in business rules and data access.
    Any recommendations? And any issues that I could face if I choose to create a new schema.
    The main reason why people (i.e. developers) hate schemas is that they impose a management overhead: granting of privliges on objects and data, in both directions, and perhaps the development of additional code such as views or API packages with restricted interfaces. In my opninon these are bogus objections, born out of slackness, but it be disineguos to deny that multiple schemas do require extra typing.

    Cheers, APC

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points