Design a normalized schema for products, customers and orders and write reporting queries.
Difficulty: Intermediate · Technology: MySQL or PostgreSQL
Features to build
- ER diagram
- Constraints and indexes
- Sales report queries
- Sample data script
Step-by-step build plan
- List entities and relationships
- Draw an ER diagram
- Create tables with keys and constraints
- Insert sample data
- Write ten reporting queries such as top products by month
The part most people find hard
Choosing the right keys and indexes.
How to test it
Run each query on a larger sample and check the speed.
Suggested extensions
Add user accounts, better error handling, automated tests and a README with screenshots. Each extension is a new talking point in interviews.
How to describe it on your resume
Designed a normalized e-commerce database and wrote analytical SQL queries.
Portfolio tip
Deploy it, link both the live version and the code, and write two paragraphs about what you learned and what you would improve.