Database naming conventions in different environments

Posted by Pramod Sadalage on Wednesday, June 10, 2015

In every enterprise and every project we end up having multiple environments, especially the database side of the enterprise tends to stick around for a longer period of time and has much more dependencies or application integration as opposed to application urls etc. Given this, how to name the servers, databases and schemas becomes a very important decision, do these names provide for an easy way to use the application and not make it harder or the developers to access the database.

Assuming we are using Oracle for our database and we have five environments other than the local developer workstations. Environments such as integration, development, qa, uat, production, how do we name the servers, database instances, schemas so that they are easy to understand and use.

Server naming conventions are generally defined and should have an easy way to identify the environment they are associated with just by looking at the name, an example would be to end the server name with a suffix for the environment procyon-i for integration, procyon-d for development, procyon-q for qa, procyon-u for uat and procyon-p for production (where procyon is name of a star, used as a servername). Instead of suffix, we could prefex the environment to the servername either way the idea is have the server name easily identify the environment.

Instance naming should follow similar convention where the name of the instance clearly shows the environment, if an instance is dedicated specifically for the application, the instance name can be same as application along with the environment suffix or prefix such as battani where battan is the database instance name and i is the integration environment, so we would have battand, battanq, battanu and battanp for development, qa, uat and production respectively. One instance and one schema

Once the instance name is setup, we can decide what the schema names are going to be, if the application is just using one schema on the instance then the schema name can be same as the application, if there are multiple instances of the application connecting to the same database instance, then we could have schema names with the purpose they are supposed to serve, such as willapa_trunk, willapa_release etc for each of the schemas.

One instance and multiple schema

In some situations we could have the need for one application instance to connect to multiple database schemas on the same instance then we should have the purpose of the schema also in the schema name such as trunk_sales, trunk_catalog etc so that its clear about what function they are serving. One instance and multiple app schemas

Following a fixed convention in the environment helps the development team to rely on predictable naming conventions thus avoiding creating excessive application configurations. The naming convention also helps the development and ops team figure out which environment is having problems based on servername, instancename or schemaname.