ShopLite project 1: requirements and schema design

60 min · Intermediate

A good schema starts from the requirements: each thing the shop must remember becomes a table, each link between things becomes a foreign key, and each business rule becomes a constraint that the database itself enforces.

What you'll build

ShopLite is a small online store that sells phones, laptops, accessories, books and clothes across India. Its owner has written down what the shop must remember and which rules must never break (the requirements below). You turn them into queries.sql, a script that creates six tables in the empty MySQL database shop that the check gives you: customers, addresses, categories, products, orders and order items.

This is the database the next sections build on: they connect an application, written in any language, to it. So every table gets a primary key, every link a foreign key and every rule a named constraint. The script ends with a query on information_schema that lists all the constraints, so you can show that each rule really exists.

Output of queries.sql

+-------------+-------------+------------------------------+
| table_name  | type        | name                         |
+-------------+-------------+------------------------------+
| addresses   | CHECK       | chk_addresses_pincode        |
| addresses   | FOREIGN KEY | fk_addresses_customer        |
| addresses   | PRIMARY KEY | PRIMARY                      |
| categories  | FOREIGN KEY | fk_categories_parent         |
| categories  | PRIMARY KEY | PRIMARY                      |
| categories  | UNIQUE      | uq_categories_name           |
| customers   | PRIMARY KEY | PRIMARY                      |
| customers   | UNIQUE      | uq_customers_email           |
| order_items | CHECK       | chk_order_items_price        |
| order_items | CHECK       | chk_order_items_quantity     |
| order_items | FOREIGN KEY | fk_order_items_order         |
| order_items | FOREIGN KEY | fk_order_items_product       |
| order_items | PRIMARY KEY | PRIMARY                      |
| order_items | UNIQUE      | uq_order_items_order_product |
| orders      | CHECK       | chk_orders_status            |
| orders      | FOREIGN KEY | fk_orders_address            |
| orders      | FOREIGN KEY | fk_orders_customer           |
| orders      | PRIMARY KEY | PRIMARY                      |
| products    | CHECK       | chk_products_price           |
| products    | CHECK       | chk_products_stock           |
| products    | FOREIGN KEY | fk_products_category         |
| products    | PRIMARY KEY | PRIMARY                      |
| products    | UNIQUE      | uq_products_sku              |
+-------------+-------------+------------------------------+

Requirements

Your program should do all of these.

A customer signs up with a full name and an e-mail that no other customer has; the phone number is optional.
A customer can save several delivery addresses (Home, Work, Hostel), each with a city, a state and a 6-digit PIN code that does not start with 0. Categories can sit inside another category: Mobiles, Laptops and Accessories are inside Electronics.
Every product has a unique SKU (stock keeping unit: the shop's own product code), one category, a price above zero and a stock that never goes below zero; old products are hidden, not deleted.
An order belongs to one customer, is delivered to one saved address and has a status: PLACED, SHIPPED, DELIVERED or CANCELLED. An order has one or more items. Each item is one product, a quantity above zero and the price paid on that day; a product appears at most once in an order.
Money is exact to the paisa. Deleting an order removes its items, but a customer, product or category that is used by an order cannot be deleted.
Use exactly these table and column names, in this order: customers (customer_id, full_name, email, phone, created_at); addresses (address_id, customer_id, label, line1, city, state, pincode, is_default); categories (category_id, name, parent_id); products (product_id, sku, name, category_id, price, stock, is_active); orders (order_id, customer_id, address_id, status, ordered_at); order_items (order_item_id, order_id, product_id, quantity, unit_price). The first column of each table is its primary key.
Name the constraints exactly as in the sample output: uq_customers_email, fk_addresses_customer, chk_addresses_pincode, uq_categories_name, fk_categories_parent, uq_products_sku, fk_products_category, chk_products_price, chk_products_stock, fk_orders_customer, fk_orders_address, chk_orders_status, uq_order_items_order_product, fk_order_items_order, fk_order_items_product, chk_order_items_quantity, chk_order_items_price.
queries.sql builds everything in one run in the current database (it does not create or switch databases), can be run again safely, and ends by listing every constraint of the current database with the columns table_name, type, name, sorted by those three.

Build it step by step in the Build tab (5 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

Next step

Start building

5 milestones

What you'll build

Output of queries.sql

+-------------+-------------+------------------------------+
| table_name  | type        | name                         |
+-------------+-------------+------------------------------+
| addresses   | CHECK       | chk_addresses_pincode        |
| addresses   | FOREIGN KEY | fk_addresses_customer        |
| addresses   | PRIMARY KEY | PRIMARY                      |
| categories  | FOREIGN KEY | fk_categories_parent         |
| categories  | PRIMARY KEY | PRIMARY                      |
| categories  | UNIQUE      | uq_categories_name           |
| customers   | PRIMARY KEY | PRIMARY                      |
| customers   | UNIQUE      | uq_customers_email           |
| order_items | CHECK       | chk_order_items_price        |
| order_items | CHECK       | chk_order_items_quantity     |
| order_items | FOREIGN KEY | fk_order_items_order         |
| order_items | FOREIGN KEY | fk_order_items_product       |
| order_items | PRIMARY KEY | PRIMARY                      |
| order_items | UNIQUE      | uq_order_items_order_product |
| orders      | CHECK       | chk_orders_status            |
| orders      | FOREIGN KEY | fk_orders_address            |
| orders      | FOREIGN KEY | fk_orders_customer           |
| orders      | PRIMARY KEY | PRIMARY                      |
| products    | CHECK       | chk_products_price           |
| products    | CHECK       | chk_products_stock           |
| products    | FOREIGN KEY | fk_products_category         |
| products    | PRIMARY KEY | PRIMARY                      |
| products    | UNIQUE      | uq_products_sku              |
+-------------+-------------+------------------------------+

Requirements

Your program should do all of these.

A customer signs up with a full name and an e-mail that no other customer has; the phone number is optional.

A customer can save several delivery addresses (Home, Work, Hostel), each with a city, a state and a 6-digit PIN code that does not start with 0. Categories can sit inside another category: Mobiles, Laptops and Accessories are inside Electronics.

Every product has a unique SKU (stock keeping unit: the shop's own product code), one category, a price above zero and a stock that never goes below zero; old products are hidden, not deleted.

An order belongs to one customer, is delivered to one saved address and has a status: PLACED, SHIPPED, DELIVERED or CANCELLED. An order has one or more items. Each item is one product, a quantity above zero and the price paid on that day; a product appears at most once in an order.

Money is exact to the paisa. Deleting an order removes its items, but a customer, product or category that is used by an order cannot be deleted.

Use exactly these table and column names, in this order: customers (customer_id, full_name, email, phone, created_at); addresses (address_id, customer_id, label, line1, city, state, pincode, is_default); categories (category_id, name, parent_id); products (product_id, sku, name, category_id, price, stock, is_active); orders (order_id, customer_id, address_id, status, ordered_at); order_items (order_item_id, order_id, product_id, quantity, unit_price). The first column of each table is its primary key.

Name the constraints exactly as in the sample output: uq_customers_email, fk_addresses_customer, chk_addresses_pincode, uq_categories_name, fk_categories_parent, uq_products_sku, fk_products_category, chk_products_price, chk_products_stock, fk_orders_customer, fk_orders_address, chk_orders_status, uq_order_items_order_product, fk_order_items_order, fk_order_items_product, chk_order_items_quantity, chk_order_items_price.

queries.sql builds everything in one run in the current database (it does not create or switch databases), can be run again safely, and ends by listing every constraint of the current database with the columns table_name, type, name, sorted by those three.