Navigating Data Modeling for a Multi-Brand Marketplace - SQL or NoSQL?

Background

While building a multi-brand e-commerce marketplace, one of the earliest and most consequential decisions was:
How should the data be modeled - using a relational (SQL) or non-relational (NoSQL) database?

The initial assumption was that customers would be able to place orders across multiple brands in a single checkout flow. This created strong cross-entity relationships between users, products, orders, and brands.

However, the model later shifted:
Each brand would effectively operate within its own self-contained “universe”, and users would place orders scoped to one brand at a time.

This shift had important consequences for how data could be structured. It opened the door to greater data isolation, favoring document-style schemas and reducing the need for complex joins.

The Initial Debate: SQL vs NoSQL

Before locking in a direction, both relational and non-relational data models were explored. The goal was to understand how each would support:

• Core features (orders, memberships, product management)
• Brand isolation vs global querying
• Developer velocity and iteration

Key Considerations

Cost Analysis: AWS Free Tiers & Operational Overhead

Early-stage architecture decisions are also financial decisions - especially when using cloud platforms like AWS.

SQL (Amazon RDS / Aurora)

• Free Tier includes 750 hours/month of db.t3.micro, great for low-traffic dev/testing.
• Managed services like RDS offer automatic backups, replication, and patching.
• May incur connection limits and IOPS charges under higher load.

NoSQL (Amazon DynamoDB)

• DynamoDB's free tier offers:
  • 25 GB storage
  • 200M read/write units/month
• Ideal for low-latency workloads and sudden traffic bursts.
  
• Pricing becomes harder to predict with diverse access patterns and high write throughput.

Impact on Evaluation

During the early stages, DynamoDB's free tier and autoscaling were appealing.
But when simulating access patterns like order lookups, product/category filtering, and inventory joins, PostgreSQL's flexibility and predictability provided better clarity - especially when iterating rapidly.

Modeling the System in Both Paradigms

NoSQL Approach (e.g., MongoDB/DynamoDB)

With each brand now living in its own "universe," NoSQL's document model started to shine:

• Brands embedded:
  • products, inventory, categories
  • memberships or tokens
• Users held:
  • brand-specific metadata (e.g., roles, preferences)
• Orders were scoped to a single brand

Pros:

• Excellent locality of data: most reads could happen in a single document
• Scaling out brands was easier: each brand could technically live in its own partition or even table

Cons:

• Still difficult to perform cross-brand analytics
• More complex indexing and access patterns when filtering across documents
• Embedding strategies required careful planning to avoid data duplication issues

SQL Approach (e.g., PostgreSQL)

Despite the move toward brand-isolated data, SQL offered consistent benefits:

• Tables for users, brands, products, orders, memberships, etc.
• Foreign keys and normalized schemas ensured consistency
• Easier to apply policies (e.g., user-scoped access by brand)

Even though brand-level scoping reduced the need for cross-brand joins, SQL still excelled in:

• Order history
• Token ownership tracking
• Reporting and analytics dashboards

AI-Assisted Tradeoff Analysis (ATAM Style)

To bring structure to the architectural decision, I followed principles of ATAM (Architecture Tradeoff Analysis Method), using AI tools like ChatGPT to:

• Simulate different access scenarios
• Identify and compare quality attributes
• Generate Mermaid ER diagrams and schema sketches
• Conduct tradeoff walk-throughs (e.g. performance vs evolvability)

ATAM Dimensions Explored

• Evolvability - How easy is schema change?
• Performance - Will it support high-throughput product views and orders?
• Consistency - How important is it to prevent stale reads?
• Analyzability - How well does each model support querying across brands/products?
• Affordability - How predictable are costs with growing usage?

Reflections on AI Tools

While AI tools like ChatGPT, Mermaid, and schema generators accelerated prototyping and scenario testing, they don't replace the need for deep domain understanding.

Success still depended on:

• Knowing real-world access patterns
• Understanding consistency vs latency tradeoffs
• Aligning data strategy with the business model

AI helped amplify human judgment, but not substitute it.

The Final Decision

Despite the brand-universe simplification, the project ultimately adopted PostgreSQL (SQL).

Key reasons:

• Continued need for reporting and analytics across entities
• Desire for robust developer tooling and ORMs
• Easier handling of edge cases and validations (e.g., unique constraints, cascade deletes)

That said, elements of NoSQL thinking were incorporated:

• Order items included product snapshots (denormalized for historical accuracy)
• Read-heavy endpoints leveraged caching layers
• Brand-specific APIs were designed to emulate data isolation per brand

Key Takeaways

• Prototype both options early - your model may evolve (as this one did!)
• AI can be a force multiplier in architectural thinking, especially in early exploration
• Domain expertise is essential - AI needs strong guidance to be useful
• Free-tier limits and cost modeling are real architectural constraints
• NoSQL is ideal when data silos naturally match business silos (like brands)
• SQL thrives when flexibility, consistency, and analytics matter

Final Thought

The question isn't “SQL or NoSQL?”
It's “What are your access patterns, and how fast will they change?”

Model accordingly - and don't be afraid to adjust course as your product evolves.