Postgresql Naming Conventions

​

Introduction

​ In this article, we will explore the benefits to a PostgreSQL database designer and Python (or really any other language) coder of using naming conventions, and how to best use them. We’ll study some examples to clearly show the practical importance of using naming conventions in both database design and writing code. ​

Why use naming conventions?

​

Readability

​ Whether you are writing a novel or an application, readability is important. Whether the database schema or code we are studying is our own or someone else’s, the easier it is to immediately get the meaning of what we are looking at, the better. The less time we spend deciphering specific words, the more quickly we understand the meaning of the bigger picture, whether that be the flow of a program’s execution or the relationships in a PostgreSQL schema. ​ Whether you are designing a database in the Postgres ecosystem, Oracle, Mongo, MS SQL Server, or even MySQL, naming conventions are important! Same idea applies to whether you are writing SQL or one of its variants. Same goes with programming languages. Whether you are using Python, Java, PHP, Javascript, Ruby, Node, C#, ASP.NET, VB.NET, B4X, or any other language. Using some kind of naming convention will make your life easier now, during development, and later, when you or someone else needs to maintain the code you wrote.

Example:

“txtNameFirst” makes more sense as a variable and database field name (hereinafter, “names”) than “a,” “b,” “string1,” and “MyVariable.” And stay tuned; we are going to take it just a bit further. Hint: “t_name_first” is arguably more readable than “txtNameFirst”. ​ Which of the following pieces of code is more readable for you? ​

​ OR ​

​

Consistency

​ When building a simple or complex application, whether you are designing a PostgreSQL database and/or writing the code that accesses that database, clarity is important and promotes efficiency. Let’s address consistency between variables in our code and field (column) names in our database. Which of the following SQL statements is more clear for you? ​

​ OR ​

​

Note: you could even go so far as to name your tables like “tblusers” instead of “Users”, as you see above. ​ The differences you see here may seem subtle, but small increases in efficiency usually compound to produce big gains in overall ease. Taking the time to think through how you name database fields and variables in the beginning will pay measurable dividends in efficiency later and your future self will thank you. _Not to mention anyone who inherits your project! ​

Counterpoint

​ That all said, there are some schools of thought that recommend not naming your database fields the same as your variable names as we advocate in this article. To be fair, each approach has its pros and cons. ​ One argument in favor of naming them differently we can see when we ask the question, “What if I want to globally replace a variable’s name but not the corresponding database field name?” I propose that that particular use case has a low probability of happening. ​ Finally, the reverse argument could be made; that a global replace – or a code search – will benefit from having this kind of consistency. Here’s an example: ​ Let’s say you named a database field and the corresponding variable as i_Count. Later you realize you want a few different kind of counters in your application. So you will want to globally replace i_Count to now be i_Count_Mega so as to make room for adding i_Count_Macro and i_Count_Micro. ​

Did you notice our break from consistency above where we used i_Count instead of i_count. With respect, this doesn’t matter. This capitalization detail is up to your personal preference. We tend to stick with lowercase, so as to reserve upper case for “important occassions,” but when would that be? Uppercase certainly stands out more. ​

Organization

​ Why would you use t_name_first and t_name_last instead of txtFirstName and txtLastName? At first, it seems like such a small difference and I’d be willing to bet you have seen far more examples of the latter usage. So as to better understand the proposed methodology, let’s break our field/variable names down into parts. ​

The structure

​ Two to three letter variable/field type designation –> High level category –> Next lower level of categorization ​ Syntax:

• type high level category next lower level category
• t Name First
• t Name Last
• t Name User
• t Phone Mobile
• t Phone Office
• i Age Dog
• i Age Cat ​ Let’s take a look at some examples where we can see the impact of using these type of naming conventions. We’ll assume here that you come from a practice of seeing benefit in using at least some structure. Here are two examples of pulling data from a PostgreSQL recordset into variables: ​

  Since Javascript is so pervasive, we’ll use its syntax in the following examples: ​

​ VS ​

​ Do you see the difference? Some benefits for both your database schema and code to notice from the above structured examples:

• More readable;
• Easier to sort;
• Easier to modify;
• Increased elegance; and
• More esthetically pleasing. ​

An alternative you may have seen in use

​ The lowercase “two to three letter variable/field type designation” you see here is an industry standard practice. Example: ​

​

Code re-use – IMPORTANT!

​ If you haven’t re-used code at some point in your career as a programmer, you will. Let’s say you are writing a new application and you want to add a sign-in screen. Why recode it when you can copy the one you built into an application two projects back? If you have standardized your naming conventions, plugging that old sign-in screen into your new application will be quite a bit easier because variables such as t_name_user, t_password, and i_level_security are named the same in both applications. ​

Conclusion

​ Reading code – whether it was written by ourselves or others – can be difficult and time consuming. Using naming conventions is one way we can increase readability, consistency, and overall organization. These factors make an impact on overall efficiency.