databases.softwareshinobi.com/landing/docs/megamart.md

Mega Mart Grocery

Schema Overview

The MEGA_MART_GROCERY database underpins the core operations of a grocery retail system, managing product categories, customer information, employee details, and sales orders including individual order items.

Table Summary

Table Name	Primary Responsibility
Categories	Organizes products into distinct categories for efficient inventory management and customer Browse.
Customers	Stores comprehensive information about all registered customers.
Employees	Contains details of all employees involved in the grocery operations.
OrderDetails	Links products to specific orders, defining the quantity of each item purchased.
Orders	Records each customer order, including who placed it, who processed it, and when.
Products	Manages the catalog of all items available for sale within the grocery store.
Shippers	Stores information about the companies responsible for delivering orders.
Suppliers	Maintains details of all product suppliers.

---

Categories Table

This table defines the various categories under which products are grouped. It is essential for organizing the product catalog and enabling filtered searches within the application.

Column Name	Data Type	Description
CategoryID	INT	Unique identifier for this entity. Used for all lookups and relationships.
CategoryName	VARCHAR(25) (Optional)	The name of the product category.
Description	VARCHAR(255) (Optional)	A brief explanation of the category.

Customers Table

This table holds the master data for all customers. It is crucial for order processing, customer relationship management, and personalized services.

Column Name	Data Type	Description
CustomerID	INT	Unique identifier for this entity. Used for all lookups and relationships.
CustomerName	VARCHAR(50) (Optional)	The full name of the customer.
ContactName	VARCHAR(50) (Optional)	The name of the primary contact person for the customer.
Address	VARCHAR(50) (Optional)	The customer's street address.
City	VARCHAR(20) (Optional)	The city where the customer resides.
PostalCode	VARCHAR(10) (Optional)	The postal code for the customer's address.
Country	VARCHAR(15) (Optional)	The country where the customer resides.

Employees Table

This table stores information about the employees of the grocery store. This data is used to track who processed an order and for internal HR functions.

Column Name	Data Type	Description
EmployeeID	INT	Unique identifier for this entity. Used for all lookups and relationships.
LastName	VARCHAR(15) (Optional)	The employee's last name.
FirstName	VARCHAR(15) (Optional)	The employee's first name.
BirthDate	DATETIME (Optional)	The employee's birth date. Map to java.time.LocalDateTime for modern date/time handling.
Photo	VARCHAR(25) (Optional)	File path or reference to the employee's photo.
Notes	VARCHAR(1024) (Optional)	General notes or additional information about the employee.

OrderDetails Table

This table captures the individual items within each order. It establishes a many-to-many relationship between orders and products, detailing quantities.

Column Name	Data Type	Description
OrderDetailID	INT	Unique identifier for this entity. Used for all lookups and relationships.
OrderID	INT (Optional)	Establishes a relationship with Orders.OrderID. Essential for data integrity and joins.
ProductID	INT (Optional)	Establishes a relationship with Products.ProductID. Essential for data integrity and joins.
Quantity	INT (Optional)	The number of units of the product included in this order detail.

Orders Table

This table records each transaction made by customers. It links customers, employees, and shippers to specific orders, providing a complete overview of each sale.

Column Name	Data Type	Description
OrderID	INT	Unique identifier for this entity. Used for all lookups and relationships.
CustomerID	INT (Optional)	Establishes a relationship with Customers.CustomerID. Essential for data integrity and joins.
EmployeeID	INT (Optional)	Establishes a relationship with Employees.EmployeeID. Essential for data integrity and joins.
OrderDate	DATETIME (Optional)	The date and time the order was placed. Map to java.time.LocalDateTime for modern date/time handling.
ShipperID	INT (Optional)	Establishes a relationship with Shippers.ShipperID. Essential for data integrity and joins.