Database Schema Design

Keys and constraints

A constraint is a rule the database itself enforces, whatever program writes to it. Constraints are the last line of defence: if the application has a bug, the database still refuses the bad row.

• PRIMARY KEY: unique and never empty. order_items has a composite one, (order_id, menu_item_id), so an item appears in an order only once.
• FOREIGN KEY: the value must exist in the other table. An order cannot belong to a user who does not exist.
• ON DELETE CASCADE: when the row referred to is deleted, delete the rows that refer to it. A session goes with its user, and an order's items go with the order: the two compositions of the class diagram (Chapter 24). Where no action is written, MySQL's default applies, which InnoDB treats as RESTRICT: the delete is refused while anything refers to the row. So a menu item that appears in any order can never be deleted, only switched off, and a user with orders can never be deleted either.
• UNIQUE: no two users share an email address; no two menu items share a name.
• NOT NULL: the value must be given. Every column in the worked schema is NOT NULL.
• DEFAULT: the value when none is given. A new order's status is placed.
• CHECK: any other rule. A price must be between Rs 1 and Rs 1,000, so between 100 and 100000 paise; a quantity between 1 and 5, as FR-7 says. MySQL has enforced CHECK constraints since version 8.0.16; before that it read them and ignored them, which is why the schema's first lines say which version it needs.

Indexes

An index lets the database find rows without reading the whole table, the way a book's index finds a page. Every primary key and unique column gets one automatically. Others are added for the questions the application asks most often:

In a composite index the order of the columns matters: the index can be used for its first column alone, or its first and second together, and so on, but not for the second column alone. The counter always asks for one day, often for one slot of it, so the day comes first.

Indexes are not free: every insert and update must also update them. Add one for a query the application really makes, not for every column.

Normalisation

Normalisation arranges the tables so that each fact is stored once. When a fact is stored twice, the two copies eventually disagree, and a change must be made in several places. The first three normal forms are the ones a mini project needs:

• First normal form (1NF): every column holds one value, and there are no repeating groups. An order's items are rows of their own in order_items, not a list in a column of orders.
• Second normal form (2NF): 1NF, and every non-key column depends on the whole key, not part of it. In order_items, whose key is the order and the item together, the quantity depends on both: how many of this item in this order. The item's name does not belong here, because it depends on the item alone; it is in menu_items.
• Third normal form (3NF): 2NF, and no non-key column depends on another non-key column. An order does not store the student's name, which depends on the user, not the order; the name is looked up in users when needed.

The worked schema is in third normal form, with two columns that look like repeats and are not accidents:

• order_items.unit_price_paise repeats the menu's price only at the moment of ordering. It is a different fact, the price this student agreed to, and must not change when the menu's price does.
• orders.total_paise can be calculated from the order's items. It is stored anyway, a deliberate step away from normalisation, because the total a student saw must be kept exactly, and it is safe because nothing ever changes an order's items once it is placed: the application has no statement that updates them.

The worked schema

This is the file the application actually loads, sql/schema.sql. Every group of the project's tests builds a fresh database from it, on MySQL 8.0 as Ubuntu 24.04 installs it, and the lab will not build unless those tests pass (Chapter 50):

-- Canteen Pre-order: the database schema.
-- Runs on MySQL 8.0.16 or later, which enforces its CHECK rules.
-- Loading it again drops every table first, so all the
-- data in them is lost: use it only to build a fresh copy.

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(80)  NOT NULL,
  email         VARCHAR(120) NOT NULL,
  password_hash VARCHAR(200) NOT NULL,
  role          ENUM('student', 'staff', 'owner')
                NOT NULL DEFAULT 'student',
  is_active     BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at    DATETIME     NOT NULL
                DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uq_users_email UNIQUE (email)
) ENGINE = InnoDB;

-- One row per signed-in browser. The id is a SHA-256 hash
-- of the cookie's value, so a copy of this table cannot be
-- used to sign in as anybody.
CREATE TABLE sessions (
  id         CHAR(64)     NOT NULL PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME     NOT NULL,
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id)
    REFERENCES users (id) ON DELETE CASCADE,
  INDEX ix_sessions_expires (expires_at)
) ENGINE = InnoDB;

-- Prices are whole paise, never rupees with a decimal
-- point: 4500 is Rs 45.00, and integers add up exactly.
CREATE TABLE menu_items (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(60)  NOT NULL,
  category     ENUM('meals', 'snacks', 'drinks', 'desserts')
               NOT NULL,
  price_paise  INT UNSIGNED NOT NULL,
  is_veg       BOOLEAN      NOT NULL,
  is_available BOOLEAN      NOT NULL DEFAULT TRUE,
  stock_left   INT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT uq_menu_items_name UNIQUE (name),
  CONSTRAINT ck_menu_items_price
    CHECK (price_paise BETWEEN 100 AND 100000)
) ENGINE = InnoDB;

-- The application writes created_at and updated_at itself,
-- from its own clock; the defaults serve rows typed by hand.
CREATE TABLE orders (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  pickup_date DATE         NOT NULL,
  pickup_slot TIME         NOT NULL,
  status      ENUM('placed', 'preparing', 'ready',
                   'collected', 'cancelled', 'no_show')
              NOT NULL DEFAULT 'placed',
  total_paise INT UNSIGNED NOT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
    REFERENCES users (id),
  INDEX ix_orders_day_slot (pickup_date, pickup_slot, status),
  INDEX ix_orders_user_day (user_id, pickup_date)
) ENGINE = InnoDB;

-- Which items an order holds. The price is copied in, so a
-- price changed tomorrow does not rewrite today's orders.
CREATE TABLE order_items (
  order_id         INT UNSIGNED     NOT NULL,
  menu_item_id     INT UNSIGNED     NOT NULL,
  quantity         TINYINT UNSIGNED NOT NULL,
  unit_price_paise INT UNSIGNED     NOT NULL,
  PRIMARY KEY (order_id, menu_item_id),
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
    REFERENCES orders (id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_item FOREIGN KEY (menu_item_id)
    REFERENCES menu_items (id),
  CONSTRAINT ck_order_items_quantity
    CHECK (quantity BETWEEN 1 AND 5)
) ENGINE = InnoDB;

The databases themselves, and the one account the application uses, are created by a second, shorter file, run once by the database administrator:

-- Run ONCE, as the MySQL administrator (root), before
-- npm run db:setup. It makes the two databases and the one
-- account the application uses, allowed into those two only.
-- Change the password here AND in your .env file.

CREATE DATABASE IF NOT EXISTS canteen
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS canteen_test
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER IF NOT EXISTS 'canteen'@'localhost'
  IDENTIFIED BY 'change-this-password';
GRANT ALL PRIVILEGES ON canteen.* TO 'canteen'@'localhost';
GRANT ALL PRIVILEGES ON canteen_test.* TO 'canteen'@'localhost';

Two details in it are decisions. utf8mb4 is the character set that can store every Unicode character, including the four-byte ones, such as emoji and some rarer scripts, which MySQL's older three-byte utf8mb3 cannot store at all: a student's name must be storable whatever it is written in. And the application's account can reach only its own two databases, the real one and the one the tests use, never the rest of the server.

Matching it to the ER diagram

Chapter 24 promised that the schema is the logical ER diagram column for column. The worked team does not check that by eye: a short script in the book's own tools reads both files and compares every table, every column, its position, its type, its keys and whether it may be empty. It reports:

schema: 5 tables, 30 columns: er.puml and schema.sql agree column for column (names, order, types, keys, NOT NULL)

The diagram abbreviates in one way only, which the script allows: INT for INT UNSIGNED, and ENUM without its list of values. A student project can make the same check by hand, table by table, and should, every time either file changes.

Do this for your project

1. Turn each entity into a table, each attribute into a typed column, each relationship into a foreign key or an associative table.
2. Choose each type deliberately; store money as whole numbers of the smallest unit.
3. Put every rule the data must obey into the schema as a constraint: NOT NULL, UNIQUE, foreign keys, CHECK.
4. Decide what happens on delete for every foreign key; the default refuses the delete.
5. Add indexes for the queries your application makes most, with the most selective, always-used column first.
6. Check each table against 1NF, 2NF and 3NF, and write down any deliberate exception with its reason.
7. Keep the schema in a SQL file in the repository, and compare it with the ER diagram whenever either changes.

Mistakes that cost marks

Money in FLOAT or DOUBLE, or in rupees with a decimal point in a JavaScript number.

No foreign keys, so orders can point at users who do not exist.

Rules only in the application, so any other program, or a bug, can write bad data.

A comma-separated list in a column: the classic breach of first normal form.

The student's name copied into every order: the classic breach of third normal form.

An index on every column, or none at all.

A schema that only exists inside a GUI tool, with no SQL file anyone can run to build it again.

Quick revision

• Entity to table, attribute to typed column, key to primary key, 1:N to a foreign key on the many side, M:N to an associative table.
• Money in whole paise: floats are inexact; JavaScript has no exact decimal.
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, CHECK; the database enforces them whatever writes to it.
• ON DELETE CASCADE for parts that die with the whole; the default refuses the delete.
• Indexes for the queries made most; column order matters in a composite index.
• 1NF one value per column; 2NF depends on the whole key; 3NF no dependency between non-key columns.
• utf8mb4 for text in any script.

Questions you must be able to answer

1. How is a many-to-many relationship represented in a relational schema? As an associative table with a foreign key to each of the two tables, usually making the pair its primary key, and holding the relationship's own attributes. Orders and menu items become order_items, with order_id, menu_item_id, the quantity and the unit price.

2. Why does the worked schema store money in paise? Because binary floating-point numbers store most fractions only approximately, so rupee amounts added up in them drift; and JavaScript, the application's language, has no exact decimal type. Whole numbers of paise are exact in MySQL, in the driver and in JavaScript.

3. What does ON DELETE CASCADE do, and where does the worked schema use it? It deletes the rows that refer to a row when that row is deleted. The worked schema uses it for a user's sessions and for an order's items, the parts that cannot exist without their whole. Everywhere else the default applies, which refuses the delete.

4. Define 1NF, 2NF and 3NF. First normal form: every column holds a single value and there are no repeating groups. Second: first normal form, and every non-key column depends on the whole primary key. Third: second normal form, and no non-key column depends on another non-key column.

5. The worked orders table stores total_paise, which can be computed. Does that break normalisation, and why was it done? It is a deliberate denormalisation: a derived value stored. It was done because the total a student agreed to must be kept exactly, and it is safe because the application never changes an order's items after the order is placed, so the stored total cannot disagree with them.

6. Why does the worked database use utf8mb4? Because it can store every Unicode character, including the four-byte ones such as emoji, which MySQL's older utf8mb3 cannot store at all, so any student's name can be stored whatever it is written in.