Anime Vault

1. Executive Summary
AnimeVault is a proposed e-commerce platform dedicated to selling rare and limited-edition anime merchandise � tees, hoodies, posters and accessories � to collectors across Zambia and the wider region. This proposal outlines the design and implementation of the relational database that will underpin the platform: its product catalogue, shopping cart, order processing, and integrated Airtel Money payment records.
The system is designed as a normalized MySQL database accessed through a PHP application layer, chosen for low hosting cost, wide availability, and straightforward deployment on standard LAMP/XAMPP infrastructure. The proposal covers the problem being solved, the objectives and scope of the database component, the full entity-relationship design, security considerations specific to handling payments and customer data, and an implementation timeline.
2. Problem Statement
Anime merchandise collectors in Zambia currently rely on informal channels � social media resellers, imported shipments arranged individually, or physical pop-up stalls � to find authentic, limited-run merchandise. These channels share the same weaknesses:
� No reliable record of stock levels, so items are frequently oversold or listings go stale.
� No structured order history, making returns, disputes, and customer support difficult to resolve.
� No integrated local payment method � many informal sellers require bank transfer or cash on delivery, which is slow and offers no automatic confirmation.
� No way to track which products are genuinely popular versus which are overstocked, since sales are not recorded systematically.
A structured database is the foundation that solves all four problems at once: it is the single source of truth for stock, orders, and payment status, and it is what allows a real storefront (with a cart, checkout, and admin dashboard) to be built on top of it.
3. Project Objectives
1. Design a normalized relational database that accurately models AnimeVault�s product catalogue, customer orders, and payments.
2. Support real-time stock tracking so that products cannot be oversold once stock reaches zero.
3. Record every payment attempt made via Airtel Money against its corresponding order, including failed and retried attempts.
4. Provide an administrative view of the data (products, orders, messages) without exposing the database directly to the public internet.
5. Keep customer and payment data secure: no plaintext passwords, no card or PIN data stored, and money values stored precisely as integers rather than floating-point numbers.
6. Build a schema that can scale from a single-shop prototype to a multi-admin, multi-warehouse operation without a redesign.
4. Scope of the Project
4.1 In Scope
� Product catalogue: categories, subcategories, and individual products with stock and pricing.
� Guest shopping cart, persisted server-side and tied to a browser session.
� Order placement, order line items, and order status tracking.
� Payment records for Airtel Money transactions, including status history.
� Contact form submissions and newsletter sign-ups.
� A single administrator role for managing products, orders, and messages.
4.2 Out of Scope (for this phase)
� Customer accounts, login, and saved order history for shoppers (currently guest checkout only).
� Multi-vendor or multi-warehouse inventory.
� Additional payment providers beyond Airtel Money (e.g. cards, other mobile money networks).
� Automated shipping/courier integration.
5. Proposed System Overview
The system follows a three-tier architecture:
� Presentation tier: server-rendered PHP pages (storefront and admin panel) styled with CSS/JS.
� Application tier: PHP business logic handling cart operations, checkout, and the Airtel Money API client (authentication, payment request, status polling).
� Data tier: a MySQL/MariaDB relational database, accessed exclusively through prepared statements.
Airtel Money is integrated as an external payment service: the application requests a payment, the customer approves it on their phone, and the result is confirmed by the application querying Airtel�s API directly � the database never trusts a payment status that has not been independently confirmed this way.
6. Stakeholders and User Roles
Role Description Database Interaction
Customer (guest) Browses products, adds to cart, checks out Read: products. Write: carts, cart_items, orders, order_items, payments
Administrator Manages catalogue, views orders and messages Full read/write: products, categories, orders, contact_messages
Payment provider (Airtel Money) External service confirming payment Indirectly updates payments and orders via the application layer
7. Functional Requirements
7. The system shall list products filterable by category and subcategory.
8. The system shall allow a customer to add, update the quantity of, and remove items from a cart without creating an account.
9. The system shall prevent a cart or order quantity from exceeding available stock.
10. The system shall generate a unique order number for every completed checkout.
11. The system shall record every Airtel Money payment attempt against an order, including its transaction reference and current status.
12. The system shall allow an administrator to add, edit, and remove products, and update order status.
13. The system shall store contact form and newsletter submissions for follow-up.
8. Non-Functional Requirements
Category Requirement
Security All queries parameterized (no SQL injection); passwords hashed with bcrypt; CSRF protection on all forms.
Data integrity Foreign key constraints enforced between orders, order items, products, and payments.
Accuracy All monetary values stored as integer minor units (ngwee) to avoid floating-point rounding errors.
Availability Database hosted on standard MySQL 5.7+/MariaDB, compatible with common shared hosting and XAMPP.
Auditability Every order retains a snapshot of product name and price at time of purchase, independent of later catalogue changes.
Performance Indexes on category, badge, and status columns to keep catalogue and order queries fast as data grows.
9. Database Design
9.1 Entity Overview
The schema is organized into three logical groups: catalogue (categories, subcategories, products), transactions (carts, cart_items, orders, order_items, payments), and support (contact_messages, newsletter_subscribers, admin_users).
9.2 Entity-Relationship Summary
� A category has many subcategories (1:M) and many products (1:M).
� A subcategory belongs to one category, and has many products (1:M).
� A cart has many cart items (1:M); each cart item references exactly one product.
� An order has many order items (1:M) and may have more than one payment attempt (1:M), covering retries after a failed payment.
� An order item stores a snapshot of the product name and price, so the historical record does not change if the product is later edited or removed.
9.3 Data Dictionary
Table Purpose Key Fields
categories Top-level product groupings id (PK), slug, name
subcategories Sub-groupings within a category id (PK), category_id (FK), name
products Catalogue items for sale id (PK), sku, name, price_cents, category_id (FK), subcategory_id (FK), stock, badge
carts One active cart per browser session id (PK), session_id
cart_items Line items within a cart id (PK), cart_id (FK), product_id (FK), quantity, unit_price_cents
orders A completed checkout id (PK), order_number, customer_name/email/phone, totals, status, payment_status
order_items Line items within an order (price snapshot) id (PK), order_id (FK), product_id (FK), product_name, quantity, line_total_cents
payments One row per Airtel Money payment attempt id (PK), order_id (FK), transaction_id, msisdn, amount_cents, status
contact_messages Contact form submissions id (PK), name, email, topic, message
newsletter_subscribers Email sign-ups id (PK), email
admin_users Administrator accounts id (PK), username, password_hash
9.4 Normalization
The schema is normalized to Third Normal Form (3NF). Category and subcategory names are stored once and referenced by foreign key rather than repeated on every product row. Order items and payments store a snapshot of price and product name specifically as an intentional, documented exception � this preserves historical accuracy (an order should always reflect what was actually charged, even if the product�s price changes afterward) rather than being a normalization oversight.
10. Technology Stack
Layer Technology
Database MySQL / MariaDB (via XAMPP for development)
Backend PHP 8, PDO with prepared statements
Frontend HTML5, CSS3, vanilla JavaScript
Payments Airtel Money (Airtel Africa Open API � Collections)
Hosting (development) Apache via XAMPP, localhost
11. Security Considerations
� All database access goes through PDO prepared statements � no raw string-concatenated SQL.
� Administrator passwords are stored as bcrypt hashes, never plaintext.
� Money is stored as integer cents/ngwee throughout, never as a floating-point type, to eliminate rounding errors.
� No card numbers or Airtel Money PINs are ever transmitted to or stored by AnimeVault � authentication happens entirely on the customer�s phone with Airtel.
� Payment status is only ever finalized after an independent server-to-server confirmation with Airtel, not from a client-submitted or webhook-claimed value alone.
� Configuration files containing database and API credentials are excluded from public web access.
12. Project Timeline
Phase Duration Key Deliverables
1. Requirements & ER Design Week 1 Finalized entity-relationship diagram, data dictionary
2. Database Implementation Week 2 MySQL schema created, seed data loaded, constraints tested
3. Backend Development Weeks 3�4 Catalogue, cart, and checkout logic built against the schema
4. Payment Integration Week 5 Airtel Money request/confirm flow connected to orders & payments tables
5. Admin Panel Week 6 Product, order, and message management interface
6. Testing & Hardening Week 7 Security review, stock/oversell testing, sample transaction testing
7. Deployment Week 8 Production database migration, go-live checklist
13. Risk Assessment
Risk Impact Mitigation
Overselling stock under concurrent orders Medium Stock decremented within the same database transaction as order creation
Payment marked paid incorrectly High Status only finalized after direct confirmation with Airtel�s API, never from client input
Data loss High Scheduled MySQL backups once live; documented restore procedure
Unauthorized admin access Medium Hashed passwords, session regeneration on login, login attempt throttling
14. Expected Outcomes
� A fully normalized MySQL database supporting the AnimeVault storefront end-to-end.
� Accurate, auditable order and payment records tied to Airtel Money transactions.
� An administrative view into stock, orders, and customer messages without direct database access.
� A schema documented well enough to extend later with customer accounts, multiple payment providers, or multi-location inventory.
15. Conclusion
This proposal presents a database design that directly answers the operational gaps in how anime merchandise is currently sold locally: no stock control, no order history, and no integrated local payment method. By building on a normalized MySQL schema with disciplined handling of money, stock, and payment confirmation, AnimeVault will have a foundation that is both trustworthy for customers and straightforward to maintain and extend as the business grows.