Stores and products
A store contains many products through its inventory records.
← Back to Work
Database Design & Development
Running Forever Shoes Database System
A relational database solution designed to support products, inventory, stores, customers, employees, sales orders, and customer product reviews for a growing specialty running-shoe retailer.
Overview
Designing a database around real business needs
Running Forever Shoes is a specialty running-shoe retailer operating multiple stores and preparing to expand its business capabilities. The database project focuses on creating a more complete system for managing product information, inventory, customers, orders, employees, and customer feedback.
The project begins with business requirements and gradually translates those requirements into conceptual, logical, and physical database models.
This process demonstrates how business rules can be converted into a structured relational database that supports future applications, reporting, and operational growth.
The Business Problem
Supporting a growing retail operation
The business needs more than a simple list of shoe brands. As Running Forever Shoes grows, the company needs a database capable of supporting detailed products, inventory by store, customer information, sales orders, employees, and product reviews.
The database must also provide a structured foundation that can support future reporting, online inventory, customer ratings, and expanded business operations.
Database Goals
- Organize manufacturers, product lines, and individual products.
- Track inventory quantities at individual store locations.
- Maintain customer and employee information.
- Record customer sales orders and individual order items.
- Support different order types.
- Store customer product ratings and reviews.
- Reduce redundant data through relational database design.
Business Rules
Translating operations into data relationships
Before defining tables and data types, the database structure was guided by business rules describing how the major entities relate to one another.
Products and manufacturers
A product is supplied through a product line associated with a manufacturer.
Customers and orders
A customer can place multiple sales orders.
Orders and products
Sales orders contain products through individual order items.
Customers and reviews
Customers can submit product reviews and ratings for products.
Stores and employees
Employees are associated with the store where they work.
Database Design Process
From business concept to implementation-ready model
The database was developed through three increasingly detailed stages. Each model builds on the previous one, moving from high-level business relationships toward a structure ready for relational database implementation.
01
Conceptual Model
Identifying the major entities
The conceptual model establishes the main business entities and the relationships between them without focusing on individual fields, data types, or implementation details.
This stage helped define the overall scope of the database and provided a clear view of how products, stores, customers, orders, employees, and reviews interact.
02
Logical Model
Defining attributes and relationships
The logical model expands the conceptual design by defining individual attributes, primary keys, foreign keys, and more detailed entity relationships.
At this stage, the database structure begins to show how the data will be organized relationally while remaining independent of specific database implementation details.
03
Physical Model
Preparing the database for implementation
The physical model translates the logical database structure into an implementation-ready schema containing field data types, required values, primary keys, foreign keys, and database constraints.
This final modeling stage provides the technical structure necessary to begin building the relational database.
Data Architecture
Organizing the system into connected data domains
Product Structure
Manufacturer → ProductLine → Product
Manufacturer information is separated from product lines and individual products so shared supplier information does not need to be duplicated across product records.
Inventory Structure
Store → Inventory → Product
Inventory functions as a linking structure between stores and products, allowing the system to track quantities and reorder levels independently for each location.
Sales Structure
Customer → SalesOrder → OrderItem → Product
Separating sales orders from order items allows one order to contain multiple products without repeating customer and order information for every purchased item.
Review Structure
Customer → ProductReview → Product
Product reviews connect customers to products while preserving rating, review text, and review-date information independently from the primary product record.
Normalization & Data Integrity
Reducing redundancy while protecting data quality
The database separates major business concepts into related tables rather than storing repeated information in a single large structure.
For example, customer and order information is stored separately from individual order items. This allows one order to contain multiple products without repeating customer, employee, store, and order data for every item purchased.
- Primary keys uniquely identify records.
- Foreign keys maintain relationships between tables.
- Linking tables support many-to-many business relationships.
- Lookup structures separate reusable classification data.
- Required fields help protect important business data.
- Constraints help maintain valid values and data integrity.
- Normalization reduces duplicated information across records.
Data Access & Security
Planning access around business responsibilities
Database security planning considers both authentication and authorization so users receive access appropriate to their responsibilities while sensitive data remains protected.
View
Authorized employees need access to product, inventory, customer, and order information required for their work.
Create
Appropriate roles may create records such as customers, orders, products, inventory entries, or reviews.
Update
Authorized users must be able to maintain changing information while limiting updates to appropriate data.
Delete
Delete permissions should be more restricted to reduce the risk of accidental or unauthorized data loss.
Database Reliability
Planning for backup and recovery
The database design process also includes planning for business interruption and data recovery. A database that supports retail operations must remain recoverable if hardware, software, or operational failures occur.
The continuity strategy considers regular backups, secure storage of backup copies, recovery procedures, and testing of restoration processes so critical business information can be recovered when necessary.
Technical Skills
Skills demonstrated through the project
Relational Database Design
SQL
MySQL
Conceptual Data Modeling
Logical Data Modeling
Physical Data Modeling
ER Diagrams
Primary & Foreign Keys
Normalization
Data Integrity
Business Rules
Requirements Analysis
Database Security
Backup & Recovery Planning
What I Learned
Turning business requirements into technical architecture
This project strengthened my ability to translate business requirements into structured technical solutions.
Developing the conceptual, logical, and physical models helped me understand how database architecture becomes progressively more detailed as a project moves from business analysis toward implementation.
The project also reinforced the importance of normalization, data integrity, security, documentation, and recovery planning when designing systems that support real business operations.
Current Outcome
A structured foundation for the final database
The project currently includes conceptual, logical, and physical database models that define the structure required to support Running Forever Shoes operations.
As the project continues, this case study will be expanded with additional implementation work, SQL development, queries, views, and the final enterprise data model.
Project currently in development.