Schema — the structure definition of a database (tables, columns, types)
Concept
Schema — the structural definition of a database: what tables exist, what columns each table has, what types those columns are, what constraints and indexes are defined.
In simple terms: The schema is the blueprint. The data is what gets built according to that blueprint.
Schema components:
- Tables (or relations): Named collections of rows with a fixed set of columns.
- Columns: Named fields with a data type (
VARCHAR(255),INT,DECIMAL(10,2),BOOLEAN,TIMESTAMP). - Data types: Constrain what values a column can hold.
- Constraints:
PRIMARY KEY: Uniquely identifies each row. Cannot be NULL.UNIQUE: No duplicate values in this column.NOT NULL: Column cannot be empty.FOREIGN KEY: References a primary key in another table. Enforces referential integrity.CHECK: Custom condition (e.g.,CHECK (age >= 18)).DEFAULT: Value when none is provided.
- Indexes: Separate data structures that speed up lookups.
Schema vs database: A "database" is the container. A "schema" is the structural definition within it. In MySQL, "schema" and "database" are synonyms. In PostgreSQL, a database contains multiple schemas (default: public).
Schema evolution: As your application changes, the schema must change too. This is done via migrations — versioned, repeatable DDL scripts.
Information schema: A system meta-schema in SQL databases (INFORMATION_SCHEMA) that stores the schema definitions — you can query INFORMATION_SCHEMA.TABLES to list all tables, INFORMATION_SCHEMA.COLUMNS to inspect columns.
Code Example
<?php
// SCHEMA DEFINITION in Laravel (via Blueprint in migration)
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
Schema::create('orders', function (Blueprint $table) {
$table->id(); // BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
$table->foreignId('user_id') // BIGINT UNSIGNED NOT NULL
->constrained() // FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
->onDelete('cascade');
$table->string('reference', 20)->unique(); // VARCHAR(20) NOT NULL UNIQUE
$table->decimal('total', 10, 2); // DECIMAL(10,2) NOT NULL
$table->enum('status', ['pending', 'paid', 'shipped', 'cancelled'])->default('pending');
$table->text('notes')->nullable(); // TEXT NULL
$table->timestamp('shipped_at')->nullable();
$table->timestamps(); // created_at, updated_at TIMESTAMP NULL
$table->softDeletes(); // deleted_at TIMESTAMP NULL
$table->index(['status', 'created_at']); // composite index
$table->index('user_id'); // single-column index
});
// The resulting SQL DDL:
/*
CREATE TABLE `orders` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NOT NULL,
`reference` VARCHAR(20) NOT NULL,
`total` DECIMAL(10,2) NOT NULL,
`status` ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
`notes` TEXT NULL,
`shipped_at` TIMESTAMP NULL,
`created_at` TIMESTAMP NULL,
`updated_at` TIMESTAMP NULL,
`deleted_at` TIMESTAMP NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `orders_reference_unique` (`reference`),
KEY `orders_user_id_index` (`user_id`),
KEY `orders_status_created_at_index` (`status`, `created_at`),
CONSTRAINT `orders_user_id_foreign`
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);
*/
// Inspecting schema at runtime
$columns = Schema::getColumnListing('orders'); // ['id', 'user_id', 'reference', ...]
$hasCol = Schema::hasColumn('orders', 'shipped_at'); // true
// Querying information_schema
$tables = \DB::select("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()");