Problem
You are the lead developer on a new project and need to choose the primary database technology. The CTO has asked you to evaluate SQL (PostgreSQL) vs NoSQL (MongoDB) options and make a recommendation backed by your analysis.
The Application
An e-commerce platform with:
- User accounts with profiles, addresses, and preferences
- Product catalog with categories, attributes, pricing, and inventory
- Orders with line items, payment status, and shipping information
- Reviews with ratings, text, and helpful votes
- Search across products with filters (price range, category, attributes)
- Analytics dashboard showing sales trends, popular products, and user behavior
Decision Factors to Evaluate
- Data Relationships: How complex are the relationships between entities? Do you need JOINs?
- Schema Flexibility: How often will the data schema change? Are there entities with varying attributes?
- Query Patterns: What types of queries will be most common? Complex aggregations? Simple key lookups?
- Consistency Requirements: Does the application require strong consistency (ACID) or is eventual consistency acceptable?
- Scale Projections: What is the expected data volume? 100 GB? 10 TB? 1 PB?
- Team Experience: What does the team already know?
Your Task
- Analyze each entity in the e-commerce application and determine whether it is better suited for SQL or NoSQL.
- Evaluate the trade-offs of each option across the decision factors above.
- Make a recommendation with clear justification.
- Identify scenarios where the opposite choice would be better.
- Consider a polyglot persistence approach (using both) and when it makes sense.
Constraints
- The team has 3 developers (small team, operational simplicity matters).
- Launch target is 3 months.
- Expected scale: 100,000 users, 50,000 products, 1,000 orders/day in year one.
- Budget: managed database service (RDS or Atlas), not self-hosted.