OraFAQ Forum: General » Naming convention - schema names

Home » Other » General » Naming convention - schema names (oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Naming convention - schema names [message #632715]

Wed, 04 February 2015 21:23

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!

Report message to a moderator

Re: Naming convention - schema names [message #632716 is a reply to message #632715]

Wed, 04 February 2015 21:31

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?

Report message to a moderator

Re: Naming convention - schema names [message #632717 is a reply to message #632716]

Wed, 04 February 2015 21:53

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.

Report message to a moderator

Re: Naming convention - schema names [message #632718 is a reply to message #632717]

Wed, 04 February 2015 21:59

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?

Report message to a moderator

Re: Naming convention - schema names [message #632726 is a reply to message #632715]

Wed, 04 February 2015 23:22

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?

Report message to a moderator

Re: Naming convention - schema names [message #632747 is a reply to message #632726]

Thu, 05 February 2015 03:37

cookiemonster
Messages: 13966
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?

Report message to a moderator

Re: Naming convention - schema names [message #632753 is a reply to message #632747]

Thu, 05 February 2015 06:47

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.

Report message to a moderator

Re: Naming convention - schema names [message #632754 is a reply to message #632753]

Thu, 05 February 2015 07:01

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

cookiemonster
Messages: 13966
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.

Report message to a moderator

Re: Naming convention - schema names [message #632761 is a reply to message #632756]

Thu, 05 February 2015 08:57

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.

Report message to a moderator

Re: Naming convention - schema names [message #632762 is a reply to message #632761]

Thu, 05 February 2015 09:01

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.

Report message to a moderator

Re: Naming convention - schema names [message #632764 is a reply to message #632761]

Thu, 05 February 2015 09:12

aimyee
Messages: 5
Registered: February 2015

Junior Member

Therefore Any more negative reasons or documtaion is appreciated

Report message to a moderator

Re: Naming convention - schema names [message #632765 is a reply to message #632762]

Thu, 05 February 2015 09:12

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?

Report message to a moderator

Re: Naming convention - schema names [message #632772 is a reply to message #632753]

Thu, 05 February 2015 09:35

gazzag
Messages: 1119
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.

Report message to a moderator

Re: Naming convention - schema names [message #632773 is a reply to message #632772]

Thu, 05 February 2015 09:39

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

Report message to a moderator

Re: Naming convention - schema names [message #632781 is a reply to message #632773]

Thu, 05 February 2015 10:50

cookiemonster
Messages: 13966
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?

Report message to a moderator

Previous Topic:	Connection
Next Topic:	Controlling session timeouts for external facing iSupplier pages

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 13 20:24:09 CDT 2025