Module 2: Database Design & Implementation

The Blueprint of Our Application

Every dynamic web application needs a database to store and retrieve information. A well-designed database is efficient, scalable, and easy to maintain. In this module, we will plan out the structure of our bakery's data and then bring it to life using SQL.

We'll be using phpMyAdmin, a graphical tool included with XAMPP, to manage our database and execute the SQL commands.

Step 1: Planning the Database Schema

First, let's identify the core components (or "entities") of our bakery management system. What information do we need to store?

• Users: To manage admins and staff who will use the system.
• Categories: To organize our products (e.g., "Cakes," "Breads," "Pastries").
• Products: The actual items we sell, each belonging to a category.
• Orders: To track customer purchases.
• Order Items: The specific products and quantities included in each order.

This plan gives us a clear structure. The `products` table will have a link (a "foreign key") to the `categories` table, and the `order_items` table will link both the `orders` and `products` tables together.

Step 2: Creating the Database in phpMyAdmin

With our plan in place, let's create the database container.

1. Open phpMyAdmin: Open your browser and go to http://localhost/phpmyadmin.
2. Create a New Database: Click on the "New" link in the left sidebar.
3. Enter Database Name: In the "Database name" field, type bakery_db.
4. Set Collation: For the collation, choose utf8mb4_general_ci. This is a good standard for handling a wide range of characters.
5. Click Create: Click the "Create" button. Your database is now ready for tables!

Step 3: Writing the SQL to Create Our Tables

Below is the complete SQL script to create all the tables we planned. This script defines each column's data type, sets primary keys for unique identification, and establishes the relationships between tables using foreign keys.

--
-- Table structure for table `users`
--
CREATE TABLE `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin','staff') NOT NULL DEFAULT 'staff',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `categories`
--
CREATE TABLE `categories` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `products`
--
CREATE TABLE `products` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `category_id` INT(11) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `price` DECIMAL(10, 2) NOT NULL,
  `image_url` VARCHAR(255),
  `stock` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `orders`
--
CREATE TABLE `orders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `customer_name` VARCHAR(100) NOT NULL,
  `total_amount` DECIMAL(10, 2) NOT NULL,
  `order_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('pending','completed','cancelled') NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `order_items`
--
CREATE TABLE `order_items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `order_id` INT(11) NOT NULL,
  `product_id` INT(11) NOT NULL,
  `quantity` INT(11) NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
                            

Step 4: Running the SQL Script

The final step is to execute this script in phpMyAdmin.

1. Make sure you have selected the bakery_db database in the left sidebar.
2. Click on the "SQL" tab at the top of the page.
3. Copy the entire SQL script from the code block above and paste it into the text area.
4. Click the "Go" button at the bottom right.

If successful, you will see a success message, and your new tables will appear in the left sidebar under the `bakery_db` database. Congratulations, your database is now structured and ready for data!