Module 7: Customer Management (CRM) | Bakery Management System Course

Building Relationships with Data

A successful bakery thrives on repeat business. Knowing your customers—who they are and what they like—is key to fostering loyalty. A Customer Relationship Management (CRM) system helps you store and manage this valuable information.

In this module, we will create a system to manage customer profiles and link them to their orders. This will allow us to see a full purchase history for any customer, opening the door for future features like loyalty programs and targeted promotions.

Step 1: Updating the Database Schema

First, we need a table to store customer information. We also need to modify our existing `orders` table to include a `customer_id`, which will link each order to a customer. A `NULL` value will allow for guest checkouts.

Run the following SQL in phpMyAdmin:

--
-- Table structure for table `customers`
--
CREATE TABLE `customers` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Modify the existing 'orders' table
--
ALTER TABLE `orders`
ADD COLUMN `customer_id` INT(11) DEFAULT NULL,
ADD FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL;

The `ON DELETE SET NULL` clause is important: if a customer profile is ever deleted, their past orders will remain in the system instead of being deleted along with the customer.

Step 2: CRUD for Customers

Next, we build the interface for managing customers. Create a file named `manage_customers.php` in the `admin/` folder. This page will function much like our previous management pages, allowing an admin to add, view, edit, and delete customer records.

admin/manage_customers.php

<?php
session_start();
require_once '../includes/db.php';
// ... (Authentication check) ...

// ... (PHP logic for adding, updating, deleting customers) ...

// Fetch all customers
$customers = $conn->query("SELECT * FROM customers ORDER BY name ASC");
?>
<h2>Manage Customers</h2>

<!-- Form to add a new customer -->
<form action="manage_customers.php" method="post">
    <input type="text" name="name" placeholder="Full Name" required>
    <input type="text" name="phone" placeholder="Phone Number">
    <input type="email" name="email" placeholder="Email Address">
    <button type="submit">Add Customer</button>
</form>

<!-- Table of existing customers -->
<table border="1">
    <thead>
        <tr><th>Name</th><th>Phone</th><th>Email</th><th>Actions</th></tr>
    </thead>
    <tbody>
        <?php while ($row = $customers->fetch_assoc()): ?>
        <tr>
            <td><?php echo htmlspecialchars($row['name']); ?></td>
            <td><?php echo htmlspecialchars($row['phone']); ?></td>
            <td><?php echo htmlspecialchars($row['email']); ?></td>
            <td>
                <a href="view_customer.php?id=<?php echo $row['id']; ?>">View History</a>
                <!-- Edit/Delete links -->
            </td>
        </tr>
        <?php endwhile; ?>
    </tbody>
</table>

Step 3: Integrating Customers into the POS

Now we update our POS interface to link orders to customers. Instead of a simple text field for a name, we can add a dropdown to select an existing customer. We'll also keep the option to add a new customer on the fly.

admin/pos.php (Modified)

<?php
// ... (session start, db connection) ...
// Fetch customers for the dropdown
$customers = $conn->query("SELECT id, name FROM customers ORDER BY name ASC");
?>

<!-- Inside the order-summary div in pos.php -->
<div class="order-summary">
    <h3>Current Order</h3>
    <ul id="cart-items"></ul>
    <h4>Total: $<span id="cart-total">0.00</span></h4>
    
    <!-- NEW Customer Selection -->
    <select id="customer-id">
        <option value="">-- Select Existing Customer --</option>
        <?php while ($customer = $customers->fetch_assoc()): ?>
            <option value="<?php echo $customer['id']; ?>">
                <?php echo htmlspecialchars($customer['name']); ?>
            </option>
        <?php endwhile; ?>
    </select>
    <p>Or, enter a new customer name for a guest checkout:</p>
    <input type="text" id="customer-name" placeholder="New/Guest Customer Name">
    
    <button id="submit-order">Place Order</button>
</div>

<!-- Your pos.js and process_order.php will need to be updated to handle
     either the customer_id from the dropdown or the new customer name. -->