SQL Database Basics: Tables, Keys, Data Types and Relationships Explained

Why Database Design Matters Before Writing Queries
Most beginners rush straight to writing SELECT queries without understanding how databases are structured. This leads to confusion when queries fail, data gets duplicated, or relationships between tables are unclear. Before you can query data effectively, you must understand how tables are designed, what data each column can store, and how tables relate to each other.
This blog covers the three fundamental pillars of SQL database design: Data Types, Constraints, and Relationships — the concepts every SQL developer uses every single day.
SQL Data Types: What Kind of Data Can Each Column Store?
Every column in a SQL table must have a defined data type. The data type tells MySQL: what kind of data the column accepts, how much storage space to allocate, and what operations (calculations, comparisons) are valid. Choosing the wrong data type wastes storage, causes errors, and slows queries.
Numeric Data Types
Data Type Storage Value Range Best Use Case
TINYINT 1 byte 0–255 (unsigned) / -128–127 (signed) Boolean-like values, ratings 1–5, age under 255
SMALLINT 2 bytes -32,768 to 32,767 Small counters, year numbers
INT / INTEGER 4 bytes -2.1 billion to 2.1 billion IDs, quantities, most integer data
BIGINT 8 bytes Very large integers (+/- 9.2 quadrillion) Social media IDs, transaction IDs
DECIMAL(p,s) Variable Exact precision decimals Prices, financial data: DECIMAL(10,2)
FLOAT 4 bytes Approximate (7 decimal digits) Scientific calculations where exact is not needed
DOUBLE 8 bytes Approximate (15 decimal digits) High-precision scientific calculations

💡 Important: Always use DECIMAL for money/prices — never FLOAT or DOUBLE. FLOAT(0.1) + FLOAT(0.2) may NOT equal 0.3 due to floating-point imprecision. DECIMAL(10,2) ensures exact values — critical for financial applications.
String (Text) Data Types
Data Type Max Length Fixed or Variable Best For
CHAR(n) 255 characters Fixed — always uses n bytes Country codes ‘IN’, gender ‘M’/’F’, fixed-length codes
VARCHAR(n) 65,535 characters Variable — uses only needed bytes Names, emails, addresses, titles
TINYTEXT 255 characters Variable Short descriptions, labels
TEXT 65,535 characters Variable Comments, descriptions, notes
MEDIUMTEXT 16 MB Variable Blog posts, documentation
LONGTEXT 4 GB Variable Large files, HTML content, JSON data
ENUM(‘a’,’b’,’c’) 65,535 options Variable Status fields: ENUM(‘active’,’inactive’,’pending’)

💡 CHAR vs VARCHAR: CHAR(10) always stores 10 bytes even for ‘Hi’ (2 chars). VARCHAR(10) stores only 3 bytes for ‘Hi’ (2 chars + 1 length byte). Use CHAR for fixed-length codes (phone country codes, currency codes). Use VARCHAR for everything else.
Date and Time Data Types
Data Type Storage Format Example Value Best Use Case
DATE YYYY-MM-DD ‘2026-01-15′ Date of birth, joining date, event date
TIME HH:MM:SS ’09:30:00’ Store opening time, appointment time
DATETIME YYYY-MM-DD HH:MM:SS ‘2026-01-15 14:32:00′ Order timestamp, log entry time
TIMESTAMP YYYY-MM-DD HH:MM:SS Auto-updates on row change last_updated fields, created_at fields
YEAR YYYY 2026 Academic year, graduation year, fiscal year

💡 DATETIME vs TIMESTAMP: TIMESTAMP automatically converts to UTC for storage and back to local time for retrieval — ideal for applications with international users. DATETIME stores exactly what you insert, no timezone conversion. For most Indian applications, either works fine.
Other Important Data Types
Data Type Description Example
BOOLEAN / TINYINT(1) True/False values (stored as 0 or 1) is_active BOOLEAN DEFAULT TRUE
JSON Stores JSON documents — flexible schema metadata JSON for extra attributes
BLOB Binary Large Object — stores binary data profile_picture MEDIUMBLOB
SET A set of string values (multi-select) notifications SET(’email’,’sms’,’push’

SQL Constraints: Rules That Enforce Data Integrity
Constraints are rules applied to columns that prevent invalid, incomplete, or duplicate data from entering the database. They are your first line of defence for data quality. Constraints are defined when creating the table (or added later with ALTER TABLE).
1. PRIMARY KEY — The Row’s Unique Identity
A Primary Key uniquely identifies each row in a table. Rules: every value must be unique, cannot contain NULL, each table can have only one PRIMARY KEY (though it can span multiple columns).
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL
);
AUTO_INCREMENT automatically assigns the next integer (1, 2, 3…) when a new row is inserted — you never need to manually assign IDs.
2. FOREIGN KEY — Linking Tables Together
A Foreign Key is a column that references the PRIMARY KEY of another table, creating a link between them. It enforces referential integrity: you cannot add an order for a customer who does not exist, and you cannot delete a customer who has orders.
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
If you try to insert an order with customer_id = 999 but no customer with ID 999 exists, MySQL will reject the INSERT with a foreign key violation error.
3. NOT NULL — Value Is Mandatory
Ensures a column cannot be left empty. Without NOT NULL, columns accept NULL (no value) by default.
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(100) NOT NULL, — REQUIRED
email VARCHAR(100), — Optional (NULL allowed)
phone VARCHAR(15) — Optional (NULL allowed)
);
4. UNIQUE — No Duplicate Values Allowed
UNIQUE ensures all values in a column are distinct. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns can (in MySQL) contain NULL values.
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100) UNIQUE NOT NULL, — No two users same email
username VARCHAR(50) UNIQUE NOT NULL, — No two users same username
phone VARCHAR(15) UNIQUE — Optional but unique if provided
);
5. DEFAULT — Automatic Value When None Provided
Specifies a value used automatically if the column is omitted during INSERT.
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
status VARCHAR(20) DEFAULT ‘active’,
stock INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
If status is not provided on INSERT, it defaults to ‘active’. If stock is not provided, it defaults to 0.
6. CHECK — Custom Validation Rules
CHECK validates that values in a column meet a specific condition before the data is accepted.
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
salary DECIMAL(10,2) CHECK (salary > 0),
age INT CHECK (age >= 18 AND age <= 65), rating TINYINT CHECK (rating BETWEEN 1 AND 5) ); SQL Constraints Summary Table Constraint Purpose NULL Allowed? Multiple Per Table? PRIMARY KEY Unique row identifier NO — must have value Only ONE per table FOREIGN KEY Links to another table's PK YES (optional relationship) YES — multiple allowed NOT NULL Value is mandatory NO — enforces this YES — on any column UNIQUE No duplicate values YES (NULLs ignored) YES — multiple allowed DEFAULT Auto-fills if not provided YES YES — on any column CHECK Custom validation condition YES (NULL passes check) YES — multiple allowedTable Relationships: How Tables Connect to Each Other The power of a relational database comes from the relationships between tables. Instead of storing all data in one giant table (which leads to duplication and errors), we split data into focused tables and connect them with foreign keys. There are three types of relationships. One-to-One (1:1) Relationship Each row in Table A corresponds to exactly one row in Table B, and vice versa. This is the least common relationship, typically used to split a table for security or performance reasons. Example: Each employee has exactly one passport. Each passport belongs to exactly one employee. CREATE TABLE passports ( passport_id INT PRIMARY KEY, emp_id INT UNIQUE NOT NULL, -- UNIQUE enforces the 1:1 passport_no VARCHAR(20) UNIQUE NOT NULL, expiry_date DATE, FOREIGN KEY (emp_id) REFERENCES employees(emp_id) ); One-to-Many (1:N) Relationship — Most Common One row in Table A can relate to many rows in Table B. But each row in Table B relates to only one row in Table A. This is the most common relationship in relational databases. Examples: One customer can have many orders (but each order belongs to one customer). One department can have many employees (but each employee belongs to one department). CREATE TABLE customers ( customer_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE );CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, -- Many orders can reference ONE customer order_date DATE NOT NULL, total DECIMAL(10,2), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); Many-to-Many (M:N) Relationship Many rows in Table A can relate to many rows in Table B. This requires a third 'junction table' (also called bridge table or pivot table) that holds the foreign keys from both tables. Examples: One student can enrol in many courses. One course can have many students enrolled. — Neither students nor courses can hold a foreign key for the other directly. CREATE TABLE students ( student_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL );CREATE TABLE courses ( course_id INT PRIMARY KEY AUTO_INCREMENT, course_name VARCHAR(100) NOT NULL, credits INT DEFAULT 3 );-- Junction table handles the Many-to-Many CREATE TABLE enrolments ( enrolment_id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, enrol_date DATE, grade DECIMAL(5,2), FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id), UNIQUE (student_id, course_id) -- Prevent duplicate enrolments ); Relationships Summary Relationship Type Description Real Example How Implemented One-to-One (1:1) Each A matches exactly one B Employee — Passport UNIQUE FOREIGN KEY in child table One-to-Many (1:N) One A matches many Bs Customer — Orders FOREIGN KEY (no UNIQUE) in child table Many-to-Many (M:N) Many As match many Bs Students — Courses Junction table with two FOREIGN KEYsComplete Mini School Database — Putting It All Together -- 1. Students CREATE TABLE students ( student_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE, dob DATE, city VARCHAR(50) DEFAULT 'India' );-- 2. Courses CREATE TABLE courses ( course_id INT PRIMARY KEY AUTO_INCREMENT, course_name VARCHAR(100) NOT NULL, credits INT DEFAULT 3 CHECK (credits BETWEEN 1 AND 6) );-- 3. Teachers CREATE TABLE teachers ( teacher_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, subject VARCHAR(100) );-- 4. Enrolments (Many-to-Many: Students-Courses) CREATE TABLE enrolments ( enrolment_id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, grade DECIMAL(5,2) CHECK (grade BETWEEN 0 AND 100), FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE, FOREIGN KEY (course_id) REFERENCES courses(course_id) ); 💡 ON DELETE CASCADE: When a student is deleted, all their enrolment records are automatically deleted too. Other options: ON DELETE SET NULL (sets FK to NULL), ON DELETE RESTRICT (default — prevents deletion if child records exist). 🔗 Internal Link: Now that you understand database structure, learn to retrieve data — SQL SELECT Statement Tutorial → https://techiebabaji.com/sql-select-statement-tutorial