The Relational Model: Tables, Rows, Columns and Schemas

Concept

The relational model stores all data in tables (formally, relations). A table has:

• Columns - named and typed. The type is a promise about every value in that column.
• Rows - one fact each. A row in employees is one employee.
• A key - one or more columns whose value identifies a row uniquely.

Tables live inside a schema, which is a named container for tables, views, indexes and routines. A schema lives inside a database instance.

Syntax

-- MySQL: a schema and a database are the same thing
CREATE DATABASE company;
USE company;

-- PostgreSQL and SQL Server: a schema sits inside a database
CREATE SCHEMA hr;
CREATE TABLE hr.employees ( ... );

Example

CREATE TABLE employees (
    id         INT           PRIMARY KEY,
    first_name VARCHAR(50)   NOT NULL,
    last_name  VARCHAR(50)   NOT NULL,
    dept_id    INT           NULL,
    salary     DECIMAL(10,2) NOT NULL
);

INSERT INTO employees VALUES (1, 'Asha',  'Nair',  10, 72000.00);
INSERT INTO employees VALUES (2, 'Ravi',  'Kumar', 20, 58000.00);
INSERT INTO employees VALUES (4, 'Karan', 'Shah',  NULL, 64000.00);

Explanation

Karan has no dept_id. That is not zero and not an empty string - it is NULL, meaning unknown or not applicable. The column allowed it because it was declared NULL. Had it been NOT NULL, the database would have rejected the row rather than storing a half truth.

Notice also that the rows were inserted as 1, 2, 4. There is no row 3 and there is no guaranteed physical order. The only order that exists in a result is the one ORDER BY creates.

Important rules

• A table is a set: no ordering, no duplicate identity. Duplicate rows are possible unless a key forbids them.
• Every column has exactly one type, and every value in it must fit that type.
• A cell holds one value. Storing 'maths,physics,chemistry' in one column breaks the model and every query that follows.
• Schema qualified names (hr.employees) remove ambiguity when two schemas hold a table with the same name.

Common mistakes

• Designing wide tables with columns like phone1, phone2, phone3. That is a repeating group; it belongs in a child table.
• Assuming SELECT * returns rows in insert order. It often looks that way on a small table, then changes the day an index is added.
• Using VARCHAR for numbers and dates. You lose type checking, sorting and every date function.

Best practices

• Name tables in the plural (employees) and columns in the singular (salary). Pick one convention and hold it.
• Use snake_case; it survives every dialect without quoting.
• Declare NOT NULL by default and allow NULL only where unknown is a real, meaningful state.

Practice

1. Sketch a books table with columns for id, title, author, published year and price. Mark which columns should be NOT NULL.
2. Explain why storing an author list as one comma separated string makes the question how many books did author X write? hard.
3. In your own words: what is the difference between a database, a schema and a table?