To meet the requirements for Miss Lei's 24/7 convenience store, the database schema
needs to store relevant information about branches, sales, sales clerks, and daily
sales transactions. Let's break down the requirements and design a schema that will
handle them efficiently.
Requirements Breakdown:
Daily Sales (peso amount) per branch: We need to store sales transactions, and link
them to specific branches. Each branch has its own daily sales record.
Daily Sales (peso amount) per sales clerk on duty: We need to associate sales
transactions with the sales clerks who handled them.
Overall Daily Sales: This will be the sum of daily sales across all branches, which
requires aggregating data from the different branch sales.
Database Schema
We can break the schema into several key entities:
Branch
Sales Clerk
Sale Transaction
Sale Details (for individual products sold in a transaction)
Schema Layout
1. Branch Table
This table will store information about each branch.
sql
Copy code
CREATE TABLE Branch (
branch_id INT PRIMARY KEY AUTO_INCREMENT,
branch_name VARCHAR(100) NOT NULL,
branch_location VARCHAR(255) NOT NULL
);
branch_id: Unique identifier for each branch.
branch_name: Name of the branch.
branch_location: Location/address of the branch.
2. Sales Clerk Table
This table will store information about each sales clerk.
sql
Copy code
CREATE TABLE SalesClerk (
clerk_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone_number VARCHAR(15)
);
clerk_id: Unique identifier for each sales clerk.
first_name: First name of the sales clerk.
last_name: Last name of the sales clerk.
email: Email address of the sales clerk (optional).
phone_number: Phone number of the sales clerk (optional).
3. Sale Transaction Table
This table stores the overall details for each transaction.
sql
Copy code
CREATE TABLE SaleTransaction (
transaction_id INT PRIMARY KEY AUTO_INCREMENT,
branch_id INT,
clerk_id INT,
sale_date DATE,
total_amount DECIMAL(10, 2),
FOREIGN KEY (branch_id) REFERENCES Branch(branch_id),
FOREIGN KEY (clerk_id) REFERENCES SalesClerk(clerk_id)
);
transaction_id: Unique identifier for each transaction.
branch_id: Foreign key referring to the branch where the sale took place.
clerk_id: Foreign key referring to the sales clerk who handled the transaction.
sale_date: The date when the transaction occurred.
total_amount: The total amount (in pesos) for this transaction.
4. Sale Details Table
This table stores detailed information about the products sold during each
transaction.
sql
Copy code
CREATE TABLE SaleDetails (
sale_detail_id INT PRIMARY KEY AUTO_INCREMENT,
transaction_id INT,
product_name VARCHAR(255) NOT NULL,
product_price DECIMAL(10, 2),
quantity INT,
amount DECIMAL(10, 2),
FOREIGN KEY (transaction_id) REFERENCES SaleTransaction(transaction_id)
);
sale_detail_id: Unique identifier for each sale detail record.
transaction_id: Foreign key referring to the SaleTransaction.
product_name: Name of the product sold.
product_price: Price per unit of the product.
quantity: Quantity of the product sold.
amount: Total amount for this product (i.e., product_price * quantity).
Sample Data
Here is how the data might look in these tables:
Branch Table: | branch_id | branch_name | branch_location |
|-----------|-------------|---------------------| | 1 | Branch 1 | Downtown | | 2 |
Branch 2 | Mall | | 3 | Branch 3 | Airport Terminal |
Sales Clerk Table: | clerk_id | first_name | last_name | email | phone_number |
|----------|------------|-----------|---------------------|--------------| | 1 |
Juan | Dela Cruz |
[email protected] | 09123456789 | | 2 | Maria | Santos |
[email protected] | 09198765432 |
Sale Transaction Table: | transaction_id | branch_id | clerk_id | sale_date |
total_amount |
|----------------|-----------|----------|------------|--------------| | 101 | 1 | 1
| 2024-11-25 | 1500.00 | | 102 | 2 | 2 | 2024-11-25 | 2500.00 |
Sale Details Table: | sale_detail_id | transaction_id | product_name |
product_price | quantity | amount |
|----------------|----------------|--------------|---------------|----------|------
---| | 1 | 101 | Chips | 50.00 | 10 | 500.00 | | 2 | 101 | Soda | 100.00 | 10 |
1000.00 | | 3 | 102 | Water | 20.00 | 10 | 200.00 | | 4 | 102 | Biscuits | 50.00 |
20 | 1000.00 |
Query Examples
Daily Sales per Branch: To get the daily sales (peso amount) for each branch, you
can use the following query:
sql
Copy code
SELECT b.branch_name, SUM(st.total_amount) AS daily_sales
FROM SaleTransaction st
JOIN Branch b ON st.branch_id = b.branch_id
WHERE st.sale_date = '2024-11-25'
GROUP BY b.branch_name;
Daily Sales per Sales Clerk: To get the daily sales per clerk, you can use:
sql
Copy code
SELECT s.first_name, s.last_name, SUM(st.total_amount) AS daily_sales
FROM SaleTransaction st
JOIN SalesClerk s ON st.clerk_id = s.clerk_id
WHERE st.sale_date = '2024-11-25'
GROUP BY s.clerk_id;
Overall Daily Sales: To get the overall daily sales:
sql
Copy code
SELECT SUM(total_amount) AS overall_daily_sales
FROM SaleTransaction
WHERE sale_date = '2024-11-25';
Final Thoughts
This schema covers all the necessary data points: branch, sales clerk, transaction
details, and sales data.
The relationships between tables are defined using foreign keys to ensure
referential integrity.
Queries can be constructed easily to generate the required reports.
The schema is designed to be flexible and scalable, with the possibility to add
more data as the business grows (e.g., adding more product categories, branches, or
sales clerks).
Let me know if you have any specific questions or need further clarifications!
