Crafting Meaningful Database Object Names in MariaDB

What are Identifiers?

In MariaDB, identifiers are names given to various database objects. These objects include:

• Stored procedures (functions stored in the database)
• Views
• Indexes
• Aliases (temporary names for tables or columns)
• Columns
• Tables
• Databases

Essentially, any named element within your MariaDB schema is considered an identifier.

Rules for Naming Identifiers

• Reserved Words
  Certain words are reserved for specific purposes within MariaDB (like SELECT or WHERE). You cannot use these words as identifiers.
• Case Sensitivity
  MariaDB is case-insensitive by default. This means that MyTable and mytable are considered the same. However, you can configure MariaDB to be case-sensitive.
• Start Characters
  The first character must be a letter or an underscore. Identifiers cannot begin with a number.
• Characters
  Identifiers can consist of letters (uppercase and lowercase), digits (0-9), underscores (_), and dollar signs ($).

Qualifying Identifiers

• You can use any combination of qualifiers depending on the context. For instance, if you're referencing a column within the current database and table, you might just use column_name.
• database_name.table_name.column_name: This is a fully qualified identifier. It specifies the column column_name within the table table_name located in the database database_name.

Additional Considerations

• Special Characters
  MariaDB doesn't allow certain special characters in identifiers, including the ASCII NUL character and characters outside the Basic Multilingual Plane (BMP).
• Numbers at the Beginning
  Identifiers starting with a number followed by "e" (e.g., 1e5) might be misinterpreted as floating-point numbers. To avoid this, enclose them in backticks () like'1e5'`.

By following these rules, you can create clear and meaningful names for your database objects in MariaDB, making your code easier to understand and maintain.

---

Creating a Database and Table

CREATE DATABASE `my_database`;  -- Database name with underscores

USE `my_database`;  -- Specifying the database

CREATE TABLE `customers` (
  `customer_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) UNIQUE NOT NULL  -- Using reserved word 'UNIQUE' for constraint
);

• We use a reserved word (UNIQUE) for a column constraint, demonstrating that identifiers are separate from reserved words.
• Each column name ( customer_id, first_name, last_name, email ) follows the identifier naming rules.
• The CREATE TABLE statement defines a table named customers.
• We use the USE statement to specify the database for subsequent operations.
• We create a database named my_database using backticks to avoid conflicts with numbers.

Selecting Data with Aliases

SELECT customer_id AS `ID`, first_name, last_name 
FROM customers;

• We assign aliases (ID for customer_id) to columns using AS. Aliases can follow the same naming rules as identifiers.
• We use the SELECT statement to retrieve data from the customers table.

SELECT * FROM other_database.orders;

• The dot notation is used to qualify the table name with the database name.
• This query selects all data (*) from the orders table located in a different database named other_database.

---

• Table Aliases
  When you assign temporary names (aliases) to existing tables within a query, this term is more appropriate.
• Column Names
  This term is specifically used when referring to the names assigned to individual data points within a table.
• Database Object Identifiers
  Similar to schema object names, but emphasizes the unique identification function of these names.
• Schema Object Names
  This is a broader term encompassing all the named elements within your database schema, including databases, tables, columns, etc.