Database (PostgreSQL) Naming and Documentation: Theory Meets Practice (Do's and Don'ts)

 🌟 Mastering Database: A Fusion of Best Practices and Practical ExamplesIn my tenure as a data/base engineer, I've discovered that the difference between good and great database management often lies in adherence to best practices, particularly in naming and commenting. Here's a distilled essence of my experience with PostgreSQL, coupled with simple examples to illustrate each guideline.

💪#########

General Naming (Mandatory):

• Do: Use lowercase, underscores, and numbers. For instance, employee_data.
• Don't: Use uppercase, spaces, or hyphens. Avoid EmployeeData or employee-data. Do not use the dollar sign ($), prohibit the use of non-English letters, and do not start with pg.
• Database Naming (Mandatory): Do: Match names with the related service. For a finance app, use finance_main_db. Don't: Use vague names like db1 or overly complex names like finance_database_2023_june.
• Role Naming (Mandatory): Do: Assign clear roles, like reporting_user. Don't: Use generic names like user1 or system names like admin.
• Schema Naming (Mandatory):Do: Use business-centric names, such as sales_reporting. Don't: Name schemas irrelevantly, like my_schema or test1.
• Table Naming (Recommended): Do: Use clear, plural nouns like customers, orders. Views use v_ as the naming prefix, materialized views use mv_ as the naming prefix, and temporary tables use tmp_ as the naming prefix. Don't: Abbreviate or use singular, like cust or order.
• Index Naming (Recommended): Do: Name indexes clearly like customer_email_idx for an index on customer emails. Don't: Leave indexes with default or ambiguous names like index1.
• Function Naming (Recommended): Do: Prefix with action, such as calculate_discount. Starting with one of select, insert, delete, update, upsert to indicate action type.Important parameters can be dictated in the function name by suffixing byids, byuser_ids. Avoid function overloading and try to keep only one function with the same name.Don't: Use vague names like function1 or multiple functions with the same name for different actions.
• Column Naming (Recommended): Do: Use descriptive names, such as created_at, email_address. Should not use reserved system column names: oid, xmin, xmax, cmin, cmax, ctid, etc.Primary key column is usually named id, or suffixed with id.The creation time is usually named created_time and the modification time is usually named updated_time.It is recommended to use is_, has_, etc. as prefixes for boolean columns.Newly added column names need to be consistent with existing column naming conventions. Don't: Use system names or reserved words like date, user.
• Variable Naming (Recommended): Do: Use clear naming in functions, like total_amount for a total in a pricing function. Don't: Use non-descriptive names like var1 or temp.
• Commenting (Essential): Do: Write concise comments, like // Checks if the user is active for an is_active column. Don't: Leave objects without comments or provide vague comments like // Column. And importantly, update your comments to reflect changes. For instance, if you change the logic behind an is_active flag to include suspended accounts, update the comment from // True if the user account is active to // True if the account is active or suspended.  
• By adhering to these best practices, we can significantly enhance the clarity and maintainability of our databases. For more insights, follow my journey in data engineering.   👍 If you find this useful, I appreciate you sharing and commenting on my thoughts. Let's grow together in our database expertise! ❤️

Follow Shoaib Rahman on LinkedIn

#PostgreSQL #DataEngineering #DatabaseBestPractices #DatabaseManagement #SQL #TechTips #DataScience #Data #BestPractices