MySQL - Naming Rules

Good naming conventions are crucial for readability, maintainability, and avoiding conflicts in MySQL (or any database).

1. General Principles

• Be descriptive: Names should clearly indicate what the table, column, or index represents.

• Be consistent: Follow the same pattern throughout your database.

• Avoid reserved words: Words like SELECT, TABLE, DATE can cause errors.

• Avoid spaces or special characters: Use _ (underscore) instead of spaces.

• Keep names lowercase: Helps avoid case sensitivity issues, especially on Linux.

2. Table Naming

• Use plural nouns for tables (optional but common)
  Example:

  CREATE TABLE users (...);
  CREATE TABLE orders (...);
  

• Prefixing can help with large projects:

  crm_customers
  crm_orders
  

• Avoid abbreviations unless widely understood.

3. Column Naming

• Be specific about what the column stores:

  first_name, last_name, email_address
  

• Use snake_case (underscores) for readability:

  date_of_birth
  

• Include units if applicable:

  weight_kg, price_usd
  

4. Primary Key & Foreign Key Naming

• Use id as primary key:

  user_id, order_id
  

• Name foreign keys clearly:

  user_id REFERENCES users(user_id)
  order_id REFERENCES orders(order_id)
  

5. Indexes and Constraints

• Include table and column names in indexes:

  idx_users_email
  uq_orders_order_number  -- for unique constraint
  fk_orders_user_id       -- for foreign key
  

6. Views, Procedures, and Functions

• Use prefixes or descriptive names:

  view_active_users
  sp_create_order
  fn_calculate_discount
  

7. Avoid Ambiguity

• Don’t use generic names like data, info, table1.

• Keep naming consistent across related tables (e.g., always user_id, never sometimes uid).

Summary of Naming Conventions