Address Management

To handle addresses effectively, it's advisable to use a service that verifies them, or alternatively, maintain a pre-approved list of addresses. Allowing users to input arbitrary addresses could lead to complications, especially in urban areas. It's essential to implement a foreign key linking the address field to the country.

DROP TABLE IF EXISTS address CASCADE;

CREATE TABLE address (

id SERIAL PRIMARY KEY,

city TEXT NOT NULL,

street_number SMALLINT NOT NULL,

street TEXT NOT NULL,

country_id INT NOT NULL,

CONSTRAINT fk_country_id

FOREIGN KEY(country_id)

REFERENCES country(id)

ON DELETE CASCADE

ON UPDATE CASCADE

);

DROP TABLE IF EXISTS user_address CASCADE;

CREATE TABLE user_address(

id SERIAL PRIMARY KEY,

address_id INT NOT NULL,

user_id INT NOT NULL,

CONSTRAINT fk_user_id

FOREIGN KEY (user_id)

REFERENCES users(id)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT fk_address_id

FOREIGN KEY (address_id)

REFERENCES address(id)

ON DELETE CASCADE

ON UPDATE CASCADE

);

User Table Structure

We will create a one-to-many relationship between users and their passwords to track changes for improved security. It may be beneficial to also include a created_at field to log when each record was added.

DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (

id SERIAL PRIMARY KEY,

first_name TEXT NOT NULL,

last_name TEXT NOT NULL,

email TEXT NOT NULL,

birthdate DATE NOT NULL,

created_at timestamptz DEFAULT NOW()

);

DROP TABLE IF EXISTS user_passwords CASCADE;

CREATE TABLE user_passwords (

id SERIAL PRIMARY KEY,

value TEXT NOT NULL,

user_id INT NOT NULL,

created_at timestamptz DEFAULT NOW(),

CONSTRAINT fk_user_id

FOREIGN KEY (user_id)

REFERENCES users(id)

ON DELETE CASCADE

ON UPDATE CASCADE

);

Using Enum Tables

Rather than creating enumerated types, we will use tables to store values and link them with foreign keys.

DROP TABLE IF EXISTS rating CASCADE;

CREATE TABLE rating (

id SERIAL PRIMARY KEY,

value SMALLINT NOT NULL UNIQUE

);

DROP TABLE IF EXISTS country CASCADE;

CREATE TABLE country (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL UNIQUE

);

DROP TABLE IF EXISTS order_status CASCADE;

CREATE TABLE order_status (

id SERIAL PRIMARY KEY,

value TEXT NOT NULL UNIQUE

);

DROP TABLE IF EXISTS currency CASCADE;

CREATE TABLE currency (

id SERIAL PRIMARY KEY,

value TEXT NOT NULL UNIQUE

);

We will populate these tables with relevant values.

INSERT INTO rating (value) VALUES (1), (2), (3), (4), (5);

INSERT INTO country (name) VALUES ('Czechia'), ('Poland');

INSERT INTO order_status (value) VALUES ('pending'), ('in_progress'), ('done');

INSERT INTO currency (value) VALUES ('eur'), ('usd'), ('pln'), ('czk');

Restaurant Table

To ensure restaurants provide valid addresses, a foreign key constraint is implemented on the address field.

DROP TABLE IF EXISTS restaurant CASCADE;

CREATE TABLE restaurant (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL,

address_id INT NOT NULL,

created_at timestamptz DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_address_id

FOREIGN KEY(address_id)

REFERENCES address(id)

ON DELETE CASCADE

ON UPDATE CASCADE

);

Delivery Driver Information

Each delivery driver will be assigned a unique ID, with the option to add personal details in a separate table.

DROP TABLE IF EXISTS delivery_driver CASCADE;

CREATE TABLE delivery_driver (

id SERIAL PRIMARY KEY,

first_name TEXT NOT NULL,

last_name TEXT NOT NULL,

created_at timestamptz DEFAULT CURRENT_TIMESTAMP

);

Order Management

The order management system is extensive, mainly due to the numerous foreign key relationships. The menu_item table will represent the dishes available for customers.

DROP TABLE IF EXISTS menu_item CASCADE;

CREATE TABLE menu_item (

id SERIAL PRIMARY KEY,

restaurant_id INT NOT NULL,

name TEXT NOT NULL,

price numeric NOT NULL,

curency_id INT NOT NULL,

created_at timestamptz DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_currency_id

FOREIGN KEY(curency_id)

REFERENCES currency(id)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT fk_restaunt_id

FOREIGN KEY(restaurant_id)

REFERENCES restaurant(id)

ON DELETE CASCADE

ON UPDATE CASCADE

);

DROP TABLE IF EXISTS "order" CASCADE;

CREATE TABLE "order" (

id SERIAL PRIMARY KEY,

user_id INT NOT NULL,

restaurant_id INT NOT NULL,

user_address_id INT NOT NULL,

driver_id INT NOT NULL,

order_status_id INT NOT NULL,

created_at timestamptz DEFAULT CURRENT_TIMESTAMP,

delivery_fee numeric NOT NULL,

total_amount SMALLINT NOT NULL,

requested_delivery_time timestamptz NOT NULL,

user_driver_rating_id SMALLINT,

user_restaurant_rating_id SMALLINT,

CONSTRAINT fk_requested_delivery_time

FOREIGN KEY(user_driver_rating_id)

REFERENCES rating(id)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT fk_user_restaurant_rating_id

FOREIGN KEY(user_restaurant_rating_id)

REFERENCES rating(id)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT fk_order_status_id

FOREIGN KEY(order_status_id)

REFERENCES order_status(id)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT fk_driver_id

FOREIGN KEY(driver_id)

REFERENCES delivery_driver(id)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT fk_user_id

FOREIGN KEY(user_id)

REFERENCES users(id)

ON DELETE CASCADE

ON UPDATE CASCADE

);

DROP TABLE IF EXISTS order_menu_item CASCADE;

CREATE TABLE order_menu_item (

id SERIAL PRIMARY KEY,

order_id INT NOT NULL,

menu_item_id INT NOT NULL,

price numeric NOT NULL,

quantity SMALLINT NOT NULL,

created_at timestamptz DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_order_id

FOREIGN KEY (order_id)

REFERENCES "order"(id)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT fk_menu_item_id

FOREIGN KEY(menu_item_id)

REFERENCES menu_item(id)

ON DELETE CASCADE

ON UPDATE CASCADE

);

The Outcome

I will include a schema diagram to illustrate how everything connects (please open it in a new tab, as it may appear small here).

The above video titled "Database Design for a Food Delivery App (Example & Walkthrough)" provides a detailed walkthrough of the design principles and implementations discussed.

To deepen your understanding, check out this next video.

The video titled "Build A Delivery App From Scratch (Step by Step Tutorial)" offers a comprehensive tutorial on constructing a delivery application from the ground up.