Sling Academy
Home/MySQL/Best practices for naming tables and columns in MySQL 8

Best practices for naming tables and columns in MySQL 8

Last updated: January 25, 2024

Introduction

Naming conventions are a critical aspect of database design. They improve readability, maintenance, and the consistency of your database schema. This tutorial dives into the best practices for naming tables and columns in MySQL 8, with a variety of code examples to illustrate the concepts.

Understanding MySQL 8 Naming Rules

Before delving into the best practices, we must understand the foundational rules for naming in MySQL 8:

  • Names can contain most characters, including numbers, but must begin with a letter or an underscore.
  • They cannot be MySQL reserved words unless quoted with backticks.
  • Names should not be longer than 64 characters.

Simple Table Naming

Example:

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Output: A table named ’employee’ is created with three columns.

Prefixed Table Naming

Example:

CREATE TABLE hr_employee (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Output: A table with a descriptive prefix ‘hr_’ indicates the Human Resources department.

Best Practices for Naming Tables

  • Use singular nouns for table names.
  • Use lowercase letters and underscores to separate words.
  • Prefix tables with a functional area or module name for clarity.

Descriptive Column Naming

Example:

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Output: The ‘order_’ prefix in column names clarifies the columns’ relationship to the order table.

Best Practices for Naming Columns

  • Be descriptive and concise.
  • Include a prefix based on the table name for foreign keys.
  • Use common abbreviations such as ‘id’ for identifier.

Using Backticks for Reserved Words

Example:

CREATE TABLE `group` (
    `group_id` INT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL
);

Output: Backticks allow the use of a reserved word ‘group’ as a table name.

Advanced Naming Techniques

For complex databases, consider:

  • Naming junction tables with the names of the tables they connect.
  • Using prefixes for denoting many-to-many relationships.
  • Abbreviating lengthy words in table or column names.

Many-to-Many Relationship Table Naming

Example:

CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY(student_id, course_id)
);

Output: Joined table ‘student_course’ illustrating many-to-many relationship.

Abbreviating Lengthy Names

Example:

CREATE TABLE cust (
    cust_id INT PRIMARY KEY,
    cust_name VARCHAR(50)
);

Output: ‘cust_’ abbreviation for ‘customer’, to simplify and shorten names.

Naming Conventions for Indexes and Constraints

Indexes and constraints should also adhere to clear naming conventions.

  • Use a consistent pattern, like idx__.
  • Name constraints with their type, such as fk__ for foreign keys.

Index Naming Example

Example:

CREATE INDEX idx_employee_last_name ON employee(last_name);

Output: An index to improve the performance of queries on ’employee’ surname.

Naming Foreign Key Constraints

Example:

ALTER TABLE hr_employee
ADD CONSTRAINT fk_department_id
FOREIGN KEY(department_id) REFERENCES department(department_id);

Output: A foreign key constraint ‘fk_department_id’ that links ‘hr_employee’ to ‘department’.

Conclusion

The use of intentional, clear, and consistent naming conventions in MySQL 8 will not only make your schema reflect best practices but also enhance the comprehensibility and development process for anyone who interfaces with the database. A well-named schema serves as self-documenting, aiding in the efficient transmission of database structure and intent.

Next Article: MySQL 8: Get a list of dates between two given dates

Previous Article: MySQL 8: Using string interpolation in a query (with examples)

Series: MySQL Tutorials: From Basic to Advanced

MySQL

You May Also Like

  • MySQL: How to reset the AUTO_INCREMENT value of a table
  • MySQL: How to add a calculated column to SELECT query
  • MySQL: Eliminate orphan rows in one-to-many relationships
  • MySQL: Using R-Tree Indexes for Spatial Data Types
  • How to Create Custom Collations in MySQL
  • Using Hash Indexes in MySQL: A Practical Guide
  • Understanding Full-Text Indexes in MySQL
  • Partial Indexes in MySQL: A Practical Guide
  • MySQL: How to Remove FOREIGN KEY Constraints
  • Using ENUM in MySQL 8: A Practical Guide (with Examples)
  • MySQL: Creating a Fixed-Size Table by Using Triggers
  • One-to-Many Relationship in MySQL 8: A Practical Guide
  • Using Regular Expressions in MySQL 8: The Complete Guide
  • Using Loops in MySQL: A Practical Guide (with Examples)
  • How to Execute an SQL File in VS Code
  • Making use of the JSON_REMOVE() function in MySQL 8
  • MySQL 8: How to count rows in related tables
  • Replication in MySQL 8: A Comprehensive Guide
  • MySQL 8: FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() functions – Explained with examples