Design and Implementation of an Enhanced Entity-Relationship (EER) Database Schema for Tracking Office-Managed Product Sales

To achieve the result shown in the image, a relational database schema was designed and represented as an Enhanced Entity-Relationship (EER) diagram. The process began by defining the core tables that represent key entities in the system: orders, customers, products, and employees. Each table contains specific columns that correspond to the attributes of these entities, such as OrderID, CustomerID, ProductID, and SalesPersonID. These tables serve as the foundation for storing and organizing transactional sales data generated within an office-managed sales environment.

Once the tables were defined, relationships were established between them using primary key–foreign key constraints. The orders table references the customers table through CustomerID, the products table through ProductID, and the employees table through SalesPersonID. These relationships formally model the real business logic present in the data: customers place orders, employees process those orders in the office, and each order involves a specific product. This relational structure ensures that sales activity can be accurately traced across all participating entities.

The directionality and cardinality of these relationships are encoded in the EER diagram through one-to-many associations derived from the key constraints. Each order is associated with exactly one customer, one product, and one employee, while each customer, product, and employee can be associated with multiple orders over time. This accurately reflects the transaction patterns observed in the dataset and provides a clear technical representation of how sales data flows through the system.

To maintain entity integrity, primary keys were assigned to each table. OrderID uniquely identifies records in the orders table, while ProductID, CustomerID, and EmployeeID uniquely identify records in their respective tables. Foreign keys were then implemented to enforce referential integrity, ensuring that every order record corresponds to valid customers, products, and employees. These constraints prevent orphaned records and maintain consistency across the database.

To improve query efficiency, indexes were applied to primary key and foreign key columns such as OrderID, ProductID, and CustomerID. Indexing these frequently accessed attributes enhances join performance and supports faster retrieval of sales data, which is essential for reporting and analysis as the dataset grows.

Normalization principles were applied to structure the schema in Third Normal Form (3NF). Customer details, employee details, and product information are stored in separate tables and referenced through keys rather than duplicated within the orders table. This design reduces redundancy, simplifies updates, and ensures that the database remains scalable and maintainable.

The completed EER diagram provides a clear and technical visualization of how office-managed product sales are recorded and related within the database. By accurately representing entities, attributes, and relationships derived from the actual data, the schema supports reliable sales tracking, historical analysis, and informed business decision-making.

This helped me to create a dashboard which led me to recommend implementing category-level margin tracking and setting minimum profit thresholds to better control profitability. To address margin erosion during high-volume periods, I suggested reducing excessive discounting and renegotiating supplier and shipping costs for low-margin items. Additionally, I adjusted inventory planning and staffing levels around historically unprofitable weeks to help reduce unnecessary operating costs. Finally, I recommended shifting marketing spend toward sub-categories and seasonal periods that consistently generated higher profit rather than focusing solely on higher sales volume.