Databases, Tables, Columns, Data Types, and Practical Schema Design
Learn how MariaDB structures information and how to design schemas that remain clean, consistent, and maintainable.
Inside this chapter
- Schema Thinking for Beginners
- Creating a Well-Structured Table
- Choosing Data Types Correctly
- A Simple Business Schema Example
Series navigation
Study the chapters in order for the smoothest path from relational foundations to production-level MariaDB operations. Use the navigation at the bottom of each page to move chapter by chapter through the full series.
Schema Thinking for Beginners
A database contains related tables. Each table stores one kind of entity or relationship, such as customers, products, orders, departments, or audit logs. Columns describe the shape of each row. A good schema design makes the data easy to validate, query, and extend later.
Beginners often put too much unrelated data in one table because it feels easier initially. That leads to duplicates, null-heavy structures, and difficult reporting. It is better to think in terms of business entities and how they connect.
Creating a Well-Structured Table
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY AUTO_INCREMENT,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
phone VARCHAR(25),
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);This table demonstrates key design basics: a stable primary key, clear naming, uniqueness on business identifiers like email, suitable string lengths, and timestamps for tracking creation.
Choosing Data Types Correctly
| Type | Use Case | Important Caution |
|---|---|---|
INT or BIGINT | Identifiers and counts | Pick ranges that support future growth |
VARCHAR(n) | Names, codes, emails | Size it intentionally, not arbitrarily huge |
TEXT | Long descriptions or documents | Less ideal for heavy indexing patterns |
DECIMAL(p,s) | Money and precise values | Avoid floating point for currency |
DATE, DATETIME, TIMESTAMP | Time-related fields | Understand timezone and auditing needs |
A Simple Business Schema Example
CREATE TABLE products (
product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
sku VARCHAR(40) NOT NULL UNIQUE,
product_name VARCHAR(150) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
is_active TINYINT(1) NOT NULL DEFAULT 1
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
customer_id BIGINT NOT NULL,
order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
order_status VARCHAR(20) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);Even this small design already shows important thinking: separate entities, relationships, business meaning, and constraints that protect data quality.