Postgresql Naming Conventions
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?
“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?
CombinedAges = JohnAge + MyIntegerForAge2;
i_age_combined = i_age + i_Age;
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?
WHERE LastName ='" + string1 + "'"
WHERE t_name_last ='" + t_name_last_ + "'"
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!
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
Did you notice our break from consistency above where we used
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.
Why would you use
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.
Two to three letter variable/field type designation –> High level category –> Next lower level of categorization Syntax:
high level category
next lower level category
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:
FirstName = rs["FirstName"];
LastName = rs["LastName"];
UserName = rs["UserName"];
MobilePhone = rs["MobilePhone"];
OfficePhone = rs["OfficePhone"];
DogAge = rs["DogAge"];
CatAge = rs["CatAge"];
CurrentScore = rs["CurrentScore"];
HighScore = rs["HighScore"];
IsSheVerified = rs["IsSheVerified"];
t_name_first = rs["t_name_first"]; // text/string
t_name_last = rs["t_name_last"];
t_name_user = rs["t_name_user"];
t_phone_mobile = rs["t_phone_mobile"];
t_phone_office = rs["t_phone_office"];
i_age_dog = rs["i_age_dog"]; // integer
i_age_cat = rs["i_age_cat"];
i_score_current = rs["i_score_current"];
i_score_high = rs["i_score_high"];
n_score_high = rs["n_score_high"]; // number (may have decimals)
is_user_verified = rs["is_user_verified"]; // boolean
b_user_verified = rs["b_user_verified"]; // boolean alternate
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:
txtNameFirst = rs["txtNameFirst"]; // text/string
intAgeDog = rs["intAgeDog"]; // integer
numScoreHigh = rs["numScoreHigh"]; // number (may have decimals)
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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started