Home » Other » General » Naming convention - schema names (oracle)
Naming convention - schema names [message #632715] Wed, 04 February 2015 21:23 Go to next message
aimyee
Messages: 5
Registered: February 2015
Junior Member
Any recommendations on naming conventions for schemas in an oracle database.
Is it a best practice to
1. Use the same schema name across environments
Example in a development environment create a schema call MPGroup
And in test create the same schema called MPGroup or
2. Differentiate the environment in the schema name.example in the development instance call the schema devMPGroup and in production call the schema prdMPGroup

If you have any documentation to support your opinion I would be grateful.
Thanks!
Re: Naming convention - schema names [message #632716 is a reply to message #632715] Wed, 04 February 2015 21:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
aimyee wrote on Wed, 04 February 2015 19:23
Any recommendations on naming conventions for schemas in an oracle database.
Is it a best practice to
1. Use the same schema name across environments
Example in a development environment create a schema call MPGroup
And in test create the same schema called MPGroup or
2. Differentiate the environment in the schema name.example in the development instance call the schema devMPGroup and in production call the schema prdMPGroup

If you have any documentation to support your opinion I would be grateful.
Thanks!


If you have different schema names, how do you do QA with different schemas in Production & pre-production environments
and then with 100% assurance to "promote" tested new features from non-production into Production status with changing schema names?
Re: Naming convention - schema names [message #632717 is a reply to message #632716] Wed, 04 February 2015 21:53 Go to previous messageGo to next message
aimyee
Messages: 5
Registered: February 2015
Junior Member
Agree however current employment is pushing option two and I'm looking for cons or pros for this approach as it is not something I've seen before.
Re: Naming convention - schema names [message #632718 is a reply to message #632717] Wed, 04 February 2015 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The "advantage" for having different schema names for DEV, TEST, QA & PROD is that they all can reside concurrently in the same database & lower licensing cost.

What is the emoticon for sarcasm?
Re: Naming convention - schema names [message #632726 is a reply to message #632715] Wed, 04 February 2015 23:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Do you have different databases or a single database?
Re: Naming convention - schema names [message #632747 is a reply to message #632726] Thu, 05 February 2015 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Depends - are these schemas that contain objects?
If so, same name, always. I can't think of a single benefit to using different names - To be absolutely clear, the ability to stick dev and test in the same DB is not a benefit and will just cause trauma if tried.

Why does business think it's a good idea?
Re: Naming convention - schema names [message #632753 is a reply to message #632747] Thu, 05 February 2015 06:47 Go to previous messageGo to next message
aimyee
Messages: 5
Registered: February 2015
Junior Member
They have different databases so that isn't the reason. I will find out today why they think it us an advantage. However I'm looking for some solid reasons it is a disadvantage.
Re: Naming convention - schema names [message #632754 is a reply to message #632753] Thu, 05 February 2015 07:01 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
If they are different databases, then by having different schema names in respective DBs will only create confusion. A small example, if a synonym is to be created, you need to change it everytime it moves into a different environment. How would developers check in their code? How would version control tool be used for the source code?

Perhaps, you could first ask the business guys why they think otherwise. Let us know their response.

[Updated on: Thu, 05 February 2015 07:03]

Report message to a moderator

Re: Naming convention - schema names [message #632756 is a reply to message #632754] Thu, 05 February 2015 07:33 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
At some point in various processes you're going to have to tell things where all the tables are.
This may be through synonyms, login details for external clients, changing the default schema on login.
If you have the same schema name in all cases all the above is static and doesn't need to be reconfigured correctly on each DB and retested every time you change anything.

Exact problems depend on external clients and how they connect.


Re: Naming convention - schema names [message #632761 is a reply to message #632756] Thu, 05 February 2015 08:57 Go to previous messageGo to next message
aimyee
Messages: 5
Registered: February 2015
Junior Member
Thanks. It is our DBA group that is recommending this practice for safety security reasons. I'm still trying to understand. Thanks.
Re: Naming convention - schema names [message #632762 is a reply to message #632761] Thu, 05 February 2015 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>It is our DBA group that is recommending this practice for safety security reasons.
The DBA group needs to be (re)trained or replaced.
The net result will be more problems than it solves.
Re: Naming convention - schema names [message #632764 is a reply to message #632761] Thu, 05 February 2015 09:12 Go to previous messageGo to next message
aimyee
Messages: 5
Registered: February 2015
Junior Member
Therefore Any more negative reasons or documtaion is appreciated
Re: Naming convention - schema names [message #632765 is a reply to message #632762] Thu, 05 February 2015 09:12 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
>It is our DBA group that is recommending this practice for safety security reasons.

Security reasons? Like?
Re: Naming convention - schema names [message #632772 is a reply to message #632753] Thu, 05 February 2015 09:35 Go to previous messageGo to next message
gazzag
Messages: 1103
Registered: November 2010
Location: Bedwas, UK
Senior Member
You cannot test your release scripts/processes properly with different schema names. So essentially any testing will be invalid.
Re: Naming convention - schema names [message #632773 is a reply to message #632772] Thu, 05 February 2015 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gazzag wrote on Thu, 05 February 2015 07:35
You cannot test your release scripts/processes properly with different schema names. So essentially any testing will be invalid.


+1
Re: Naming convention - schema names [message #632781 is a reply to message #632773] Thu, 05 February 2015 10:50 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have they considered changing the password?
Or better yet, if the external applications don't log onto the main schema directly, locking the account?
Previous Topic: Connection
Next Topic: Controlling session timeouts for external facing iSupplier pages
Goto Forum:
  


Current Time: Wed Dec 01 09:02:36 CST 2021