Designing a Scalable E‑Commerce Data Model: From Products to Orders and Promotions

Preface

I have built an offline e‑commerce data system for Taobao for over two years and still feel I have much to learn; this article consolidates my experience and invites discussion for mutual improvement.

Product Module Design

The product module is the core of the architecture; a poor design hampers complex statistical needs.

Key points include separating brand from category to reduce attribute‑related complexity, storing SKUs in a product SKU table with additional fields such as ProductCode and BarCode, and splitting basic attributes into a product basic attribute value table while keeping SKU‑specific attributes separate.

Attribute search is controlled via a flag in the attribute name table, distinguishing searchable attributes from others, and differentiating basic attributes (shared by all SKUs of a product) from sales attributes (specific to each SKU).

Packaging (bundling) combines multiple SKUs into a single sale unit for promotional purposes; the design treats both single‑SKU and multi‑SKU bundles uniformly, distinguished by a type field.

Price changes must be recorded in a product change log, and any modification to a bundle’s price requires recalculating the prices of its constituent SKUs.

Order Module Structure

The order schema emphasizes three aspects:

Store address library ID plus the specific buyer address in the order main table to avoid reliance on mutable user address data.

Include city‑level information from the address library for statistical dimensions.

In the order detail table, store the package ID, the product title, SKU name, and the price at the time of purchase, as well as a promotion information table that records all applied promotional activities.

Activity Module Design

Promotional activities are linked to orders via an activity‑promotion table, enabling straightforward aggregation of activity‑specific order data. Front‑end activity caching must verify activity validity during both product display and order submission.

Visit Tracking and CRM

Visit data combines third‑party analytics with internal tracking to support conversion‑rate analysis, traffic source evaluation, and content optimization.

The CRM effort focuses on acquiring, retaining, and growing profitable customers, with current emphasis on repeat‑purchase rates and brand loyalty.

What Makes a Good Data Report

A quality two‑dimensional data report should provide:

Visually appealing charts.

Corresponding data tables.

Clear terminology explanations.

Cross‑linked business reports.

Business‑process flow visualization.

Afterword

Increasing focus on operations, marketing, and business models makes technology feel less central, but continuous effort remains essential.