Databases, Tables, Temporary Objects, Views, and Namespace Conventions

Learning outcomes

ServiceHub is ready to evolve from a small Chapter 01 schema into a multi-team application. A developer proposes tables named WorkOrder and workorder because both names work on a Linux laptop. Another developer creates a temporary table called customers and is surprised when ordinary queries stop seeing the permanent table. A third uses “schema” as though MariaDB had PostgreSQL-style schemas inside a database. None of these problems is about difficult SQL; they are namespace mistakes that can survive development and fail during deployment.

MariaDB uses database and schema as synonyms for the same namespace. Tables, views, sequences and other objects live within that database namespace, while temporary tables are session-scoped objects that can shadow permanent names. Object-name case behavior is partly tied to the underlying filesystem and to lower_case_table_names, which is an initialization-time choice. This lesson turns those facts into safe naming and qualification habits.

1. DATABASE and SCHEMA name the same namespace in MariaDB

In MariaDB, CREATE DATABASE and CREATE SCHEMA are equivalent ways to create the same kind of namespace. This differs from PostgreSQL, where one server can contain multiple databases and each database then contains schemas. A MariaDB table is commonly named with two parts—database_name.table_name—and USE database_name selects the default database for unqualified names.

CREATE DATABASE IF NOT EXISTS servicehub_sandbox;SHOW DATABASES LIKE 'servicehub_sandbox';USE servicehub_sandbox;SELECT DATABASE() AS current_database;CREATE TABLE namespace_probe (id INT PRIMARY KEY) ENGINE=InnoDB;SHOW FULL TABLES;

The server does not create a nested PostgreSQL-style schema inside servicehub_sandbox. The database itself is the namespace. For administrative scripts, migrations and cross-database joins, explicit qualification such as servicehub.work_orders is often clearer than relying on an implicit USE that may differ between sessions.

2. Qualification prevents “wrong database” accidents

An unqualified statement such as DELETE FROM work_orders acts on the table resolved in the current default database. In an interactive client, the prompt may remind you of the selected database, but application pools and migration runners can reconnect with different defaults. Production-safe tooling should therefore verify DATABASE() before destructive work and qualify important objects in migration scripts.

SELECT DATABASE() AS current_database,       CURRENT_USER() AS authenticated_account,       @@hostname AS server_host,       @@port AS server_port;SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA IN ('servicehub','servicehub_sandbox')  AND TABLE_NAME='work_orders';

These checks answer four different questions: which default namespace the session uses, which MariaDB account/host mapping authenticated it, which server accepted the connection, and which candidate objects actually exist. The evidence is much stronger than “the connection string looks right.”

3. Temporary tables are private session objects—and can shadow names

A temporary table created with CREATE TEMPORARY TABLE belongs to the current session and is automatically removed when that session ends. A temporary table may have the same name as a permanent table. While the temporary object exists, unqualified references in that session resolve to the temporary table, effectively hiding the permanent one. This is useful for staging intermediate data, but dangerous when a developer forgets that name resolution changed.

USE servicehub_sandbox;DROP TABLE IF EXISTS route_cache;CREATE TABLE route_cache (  route_id INT PRIMARY KEY,  source_name VARCHAR(20) NOT NULL) ENGINE=InnoDB;INSERT INTO route_cache VALUES (1,'permanent');CREATE TEMPORARY TABLE route_cache (  route_id INT PRIMARY KEY,  source_name VARCHAR(20) NOT NULL) ENGINE=InnoDB;INSERT INTO route_cache VALUES (1,'temporary');SELECT * FROM route_cache;        -- sees the temporary tableDROP TEMPORARY TABLE route_cache;SELECT * FROM route_cache;        -- permanent table is visible again

The second SELECT returning different data is not corruption. It is namespace resolution. The temporary table existed only in this session; another concurrent session continued to see the permanent table. That difference is exactly why temporary object names should be intentionally distinctive in complex scripts.

4. Views are named query interfaces, not copied tables

A view stores a query definition and exposes it through a table-like name. It can hide join complexity or present a stable interface while underlying tables evolve, but it does not automatically materialize and refresh a second copy of the data. MariaDB view behavior also involves DEFINER and SQL SECURITY, which become security-sensitive during dump/restore and migration; Chapter 11 covers that boundary in depth.

CREATE OR REPLACE VIEW servicehub_sandbox.open_route_cache ASSELECT route_id, source_nameFROM servicehub_sandbox.route_cacheWHERE source_name <> 'retired';SHOW CREATE VIEW servicehub_sandbox.open_route_cache;SHOW FULL TABLES FROM servicehub_sandbox;SELECT TABLE_NAME, TABLE_TYPEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA='servicehub_sandbox'ORDER BY TABLE_NAME;

SHOW FULL TABLES and INFORMATION_SCHEMA.TABLES distinguish base tables from views. Prefer these supported metadata surfaces over deriving object type from naming suffixes such as _view. Names are for humans; metadata is server evidence.

5. Case sensitivity is a deployment property, not a style preference

MariaDB database and table names can behave differently across operating systems because those identifiers can map to directories/files. Current MariaDB documentation describes Unix-like systems as typically case-sensitive and Windows as typically case-insensitive. The system variable lower_case_table_names controls important aspects of storage/comparison, but it is an initialization parameter: it must be chosen before the system databases are initialized. Changing it on an established data directory is not a normal runtime tuning action.

SHOW VARIABLES LIKE 'lower_case_table_names';SHOW VARIABLES LIKE 'lower_case_file_system';SELECT @@version, @@version_comment;SELECT TABLE_SCHEMA, TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA='servicehub_sandbox'ORDER BY TABLE_NAME;

The safest cross-platform convention is simple: use one predictable case—commonly lowercase snake_case—for database and table identifiers, avoid creating names that differ only by case, and test migrations on the same case policy that production uses. Consistent quoting is also important: identifiers and string literals are not interchangeable.

-- Do not build a portable schema around case distinctions like these.CREATE TABLE WorkOrder (id INT PRIMARY KEY);CREATE TABLE workorder (id INT PRIMARY KEY);-- Whether both can coexist depends on platform/configuration.-- Safer policy: one canonical name such as work_order.

6. Namespace conventions for ServiceHub

Good naming conventions reduce ambiguity without pretending names enforce security. For ServiceHub, keep application objects in a clearly owned database such as servicehub; use lowercase snake_case; use singular/plural consistently; avoid reserved words and punctuation-heavy identifiers; qualify objects in operational scripts; and give temporary objects a recognizable prefix such as tmp_ when that improves readability.

7. Hands-on lab and verification checklist

Use only servicehub_sandbox for this exercise. The cleanup statements are included so the lab leaves no application data behind.

1. Create servicehub_sandbox and verify DATABASE().
2. Create a lowercase permanent table and query it through both qualified and unqualified names.
3. Create a temporary table with the same name in one session and prove that it shadows the permanent table only there.
4. Drop the temporary table and prove the permanent table becomes visible again.
5. Create a simple view and distinguish it from the base table using SHOW FULL TABLES and INFORMATION_SCHEMA.TABLES.
6. Inspect lower_case_table_names and record the host policy; do not change it.
7. Drop the sandbox view/table/database after verifying you are connected to the disposable target.

8. Summary and bridge

MariaDB namespace safety begins with a precise model: database and schema are synonyms; qualification selects an object explicitly; temporary tables are session-scoped and may shadow permanent names; views are stored query interfaces; and database/table case behavior is partly tied to platform and initialization policy. These are correctness boundaries, not cosmetic conventions.

The next lesson moves from object names to the values stored inside them. You will choose numeric, string, temporal, UUID, JSON, vector, spatial and binary types from domain requirements instead of copying familiar declarations from another database product.