The schema is the product. The storefront is just how you look at it.
A fully normalized MySQL database with PL/SQL stored procedures, automated triggers, and ACID-compliant transactions - with a complete PHP storefront and admin dashboard built on top.
An e-commerce backend has to survive concurrency, not just look correct on paper.
Inventory, suppliers, and orders all change at once from multiple places. A schema that isn't properly normalized either duplicates data until it drifts out of sync, or falls apart under concurrent writes - and both failures reach the customer as a wrong stock count.
- Redundant data drifts. Denormalized product and supplier records mean an update can silently miss a copy, and the storefront starts advertising things that aren't there.
- Two customers, one last unit. Simultaneous checkout is a correctness problem that has to be solved at the transaction layer, not with application-level guesswork.
- Business rules leak. If stock decrements live only in PHP, any new code path that forgets to call them corrupts the inventory.
Model it properly, enforce it in the database, then build the store.
ERD design
Entity-relationship modelling for products, brands, suppliers, orders, and customers before a single table existed.
Normalize to 3NF
No repeating groups, no partial dependencies, no transitive dependencies - redundancy designed out rather than patched.
Triggers & stored procedures
PL/SQL enforces stock decrements and integrity checks at the data layer, so no application path can skip them.
Transactional checkout
ACID transactions wrap order placement so concurrent checkouts can't oversell.
Storefront & admin
PHP storefront with brand filtering and cart management, plus a secure dashboard for orders and inventory.
What the design actually guarantees.
Redundancy designed out, not cleaned up later
Third normal form across products, brands, suppliers, and inventory means a fact is stored once and updated once - the drift problem stops being possible rather than being monitored for.
Rules the application layer can't bypass
Stock control and audit logging run as PL/SQL triggers and stored procedures, so they fire regardless of which code path placed the order.
Checkout is transactional
ACID-compliant transactions wrap order placement, making simultaneous purchases of the last unit a resolved case rather than a race.
A working store, not a diagram
Brand-filtered catalogue pages, cart and checkout flow, order history, and an admin dashboard covering active orders, inventory, and product management.
Entity-Relationship Diagram (ERD).
The core tables mapped out as an entity-relationship diagram, showing the relationships that drive the store.
erDiagram
customer ||--o{ cart : "fills"
customer ||--o{ orders : "places"
orders ||--o{ order_item : "contains"
perfumes ||--o{ cart : "sits in"
perfumes ||--o{ order_item : "sold as"
customer {
int id PK
string username UK
string email UK
string password
string name
string contacts
string address
date dob
tinyint admin_check "1 = shop owner"
}
perfumes {
int id PK
string name UK
text perfume_notes
text description
string volume
int qty "stock"
string image_path
decimal price "in Taka"
tinyint trending "1 = on homepage"
tinyint status "1 = visible"
}
cart {
int id PK
int user_id FK
int perfume_id FK
int perfume_qty
}
orders {
int id PK
string tracking_no UK
int user_id FK
string name
string email
string address
string zipcode
decimal total_price
string payment_mode
tinyint status "0 to 4"
timestamp created_at
}
order_item {
int id PK
int order_id FK
int perfume_id FK
int perfume_qty
decimal price "price when bought"
}A look around the live application.
