home
Big Data Evolution Spark Basics PySpark RDD DataFrames & Catalyst Basic Operations Data Sources & Formats Advanced Transformations Spark Core & Execution Performance Tuning Structured Streaming Delta Lake / Lakehouse Advanced Projects
About Data Modelling Types of Data Modelling Relational System - Modelling Reporting System - Modelling
Case Studies
LinkedIn System Facebook System WhatsApp System Instagram System Amazon Shopping Ride Hailing (Uber/Ola) Garage System Parking System Netflix System
About Data Governance Objectives of Data Governance Implementation Strategy (How)
Case Studies
Governance First Principles Healthcare Governance Financial Governance Retail Loyalty Governance
Introduction Architecture Virtual Warehouses
Introduction Airflow Architecture Operators & Sensors Advanced Concepts Writing First DAG Production Best Practices
Azure Data Engineer
Evolution of Data Engineering Changing Practices Mastering DE with AI Interview Scenarios AI-DLC Methodology
AWS Lambda Mechanics AWS S3 Case Study DynamoDB Mechanics
diamond Go Premium
person_outline
Hello Guest
Sign in to access all content
Information
About Us Contact Us
Settings & Preferences
App Theme
light_mode Light Mode dark_mode Dark Mode
Account
Sign In / Login
schema Data Modelling
search
  • About Data Modelling
  • Types of Data Modelling
  • Relational System Modelling
  • Reporting System Modelling
    • keyboard_arrow_down Case Studies
    Data Engineering Path  ·  Data Modelling
    AMAZON SHOPPING CASE STUDY

    Step 2: Entity Identification — Amazon (Shopping)

    Banner

    Detailed Entity Analysis

    💡 Click on any entity below to expand and view its columns and business rules.

    🔹 USERS — Customer account credentials and prime status tracking
    Attribute Data Type Description
    user_id INT (PK) Unique auto-increment identifier
    email VARCHAR(255), UNIQUE Login address
    phone_number VARCHAR(20), UNIQUE SMS authentication number
    password_hash VARCHAR(255) Salted password credentials
    full_name VARCHAR(100) Profile display name
    is_prime BOOLEAN Toggles prime expedited shipping benefits
    prime_joined_at TIMESTAMP Join date for analytics
    created_at TIMESTAMP Timestamp of creation
    updated_at TIMESTAMP Timestamp of update
    🔹 SHIPPING_ADDRESSES — Multiple delivery addresses per user
    Attribute Data Type Description
    address_id INT (PK) Unique address ID
    user_id INT (FK) User reference
    address_line1 VARCHAR(150) Street address details
    address_line2 VARCHAR(150) Appt/Suite
    city VARCHAR(100) City name
    state VARCHAR(100) State/Province
    postal_code VARCHAR(20) ZIP/Postal code
    country VARCHAR(100) Country
    is_default BOOLEAN Toggles fast one-click checkout
    🔹 PAYMENT_METHODS — Vaulted payment details and tokens
    Attribute Data Type Description
    payment_method_id INT (PK) Unique method ID
    user_id INT (FK) Customer reference
    provider VARCHAR(50) e.g., VISA, Mastercard, PayPal
    tokenized_card VARCHAR(255) Secure token (never raw card data)
    expiration_date DATE Card expiry
    is_default BOOLEAN Default payment toggle
    🔹 SELLERS — Business registration and merchant profiles
    Attribute Data Type Description
    seller_id INT (PK) Unique seller ID
    business_name VARCHAR(150), UNIQUE Business display name
    contact_email VARCHAR(255) Contact email
    tax_id VARCHAR(50), UNIQUE Tax registration number
    rating DECIMAL(3,2) Average seller rating
    is_verified BOOLEAN Verification badge
    created_at TIMESTAMP Registration date
    🔹 CATEGORIES — Hierarchical product categories (self-referential)
    Attribute Data Type Description
    category_id INT (PK) Unique category ID
    parent_category_id INT (FK, Self) Parent category (NULL = root category)
    name VARCHAR(100) Category display name
    description TEXT Category description

    Business Rules:

    • Self-referencing FK enables tree structure (Electronics → Computers → Laptops).
    • Root categories have parent_category_id = NULL.
    🔹 PRODUCTS — Master product catalog entries
    Attribute Data Type Description
    product_id INT (PK) Unique product ID
    seller_id INT (FK) Selling merchant
    category_id INT (FK) Product category classification
    title VARCHAR(255) Product title
    description TEXT Detailed description
    brand VARCHAR(100) Brand name
    created_at TIMESTAMP Listing creation timestamp
    🔹 PRODUCT_VARIANTS — SKU-level variations (size, color, storage)
    Attribute Data Type Description
    variant_id INT (PK) Unique variant ID
    product_id INT (FK) Master product catalog reference
    sku VARCHAR(100), UNIQUE Stock Keeping Unit (e.g. IPH15-BLK-128)
    price DECIMAL(10,2) Variant price
    attributes JSONB Structural details (e.g. {"color": "black", "storage": "128GB"})
    weight_kg DECIMAL(6,2) Used for computing shipping fees
    created_at TIMESTAMP Creation time

    Business Rules:

    • sku must be globally unique to ensure scan accuracy.
    • Prices must be positive values.
    🔹 PRODUCT_MEDIA — Product images and video showcases
    Attribute Data Type Description
    media_id INT (PK) Unique media ID
    product_id INT (FK) Associated product
    media_url VARCHAR(512) CDN link to image/video
    position INT Display order on product page
    🔹 FULFILLMENT_CENTERS — Warehouses storing inventories
    Attribute Data Type Description
    center_id INT (PK) Unique warehouse ID
    name VARCHAR(100) Warehouse display name
    location_code VARCHAR(20), UNIQUE Internal location code
    address VARCHAR(255) Physical address
    🔹 INVENTORIES — Stock levels per variant per warehouse
    Attribute Data Type Description
    inventory_id INT (PK) Unique inventory entry ID
    variant_id INT (FK) Linked product variant
    center_id INT (FK) Warehouse location reference
    quantity_available INT Quantity ready to sell
    quantity_reserved INT Quantity in active checkout carts / pending orders

    Business Rules:

    • Composite unique constraint on (variant_id, center_id).
    • quantity_reserved increments during checkout, decrements on order cancellation.
    🔹 CARTS — Active customer shopping baskets
    Attribute Data Type Description
    cart_id INT (PK) Unique cart ID
    user_id INT (FK, UNIQUE) One active cart per customer
    created_at TIMESTAMP Cart creation time
    updated_at TIMESTAMP Last modification time
    🔹 CART_ITEMS — Items inside a customer's basket
    Attribute Data Type Description
    cart_item_id INT (PK) Unique line item ID
    cart_id INT (FK) Parent cart
    variant_id INT (FK) Product variant
    quantity INT Number of units
    created_at TIMESTAMP Added timestamp

    Business Rules:

    • Composite unique constraint on (cart_id, variant_id) — prevents duplicate line items.
    🔹 ORDERS — Transaction records for purchases
    Attribute Data Type Description
    order_id INT (PK) Unique order reference
    user_id INT (FK) Purchasing customer
    shipping_address_id INT (FK) Destination address
    status ENUM PENDING, PAID, SHIPPED, DELIVERED, CANCELLED
    total_amount DECIMAL(12,2) Final billing sum
    tax_amount DECIMAL(10,2) Tax charges
    shipping_cost DECIMAL(8,2) Shipping fees
    created_at TIMESTAMP Order placed timestamp
    🔹 ORDER_ITEMS — Snapshots of purchased items
    Attribute Data Type Description
    order_item_id INT (PK) Line item ID
    order_id INT (FK) Order reference
    variant_id INT (FK) Product variant reference
    quantity INT Number purchased
    price_per_unit DECIMAL(10,2) Snapshot purchase price

    Business Rules:

    • price_per_unit is a snapshot — preserving the price at the time of purchase even if the catalog price later changes.
    🔹 PAYMENTS — Transaction processing records
    Attribute Data Type Description
    payment_id INT (PK) Unique payment ID
    order_id INT (FK, UNIQUE) One-to-one with order
    payment_method_id INT (FK) Payment method used
    transaction_reference VARCHAR(255), UNIQUE Gateway transaction ID
    amount DECIMAL(12,2) Amount charged
    status VARCHAR(50) SUCCESS, FAILED, REFUNDED
    processed_at TIMESTAMP Processing timestamp
    🔹 ORDER_SHIPMENTS — Shipping and logistics metadata
    Attribute Data Type Description
    shipment_id INT (PK) Unique shipment ID
    order_id INT (FK) Parent order
    carrier VARCHAR(50) FedEx, DHL, USPS, Amazon Logistics
    tracking_number VARCHAR(100), UNIQUE Public tracking ID
    status ENUM IN_TRANSIT, OUT_FOR_DELIVERY, DELIVERED, RETURNED
    estimated_delivery DATE Estimated delivery date
    shipped_at TIMESTAMP Ship time
    🔹 SHIPMENT_TRACKING_LOGS — Package transit event chronology
    Attribute Data Type Description
    log_id INT (PK) Unique log entry ID
    shipment_id INT (FK) Parent shipment
    location VARCHAR(150) Transit hub / facility name
    activity_description VARCHAR(255) Event (e.g. "Arrived at sort facility")
    logged_at TIMESTAMP Event timestamp
    🔹 REVIEWS — Customer product ratings and reviews
    Attribute Data Type Description
    review_id INT (PK) Unique review ID
    product_id INT (FK) Reviewed product
    user_id INT (FK) Reviewer
    rating INT 1 to 5 stars
    headline VARCHAR(150) Review title
    comment TEXT Review body
    verified_purchase BOOLEAN Whether reviewer actually purchased
    created_at TIMESTAMP Review post time
    🔹 REVIEW_HELPFUL_VOTES — "Was this review helpful?" tracking
    Attribute Data Type Description
    review_id INT (FK) Target review
    user_id INT (FK) Voter
    created_at TIMESTAMP Vote timestamp

    Business Rules:

    • Composite PK (review_id, user_id) — one vote per user per review.
    🔹 COUPONS — Promotional discount definitions
    Attribute Data Type Description
    coupon_id INT (PK) Unique coupon reference
    code VARCHAR(50), UNIQUE Code typed by user (e.g. AMZ50)
    discount_type ENUM PERCENTAGE, FLAT
    value DECIMAL(10,2) Deduction amount
    max_discount DECIMAL(10,2) Maximum discount limit
    start_date TIMESTAMP Start validity
    end_date TIMESTAMP Expiration timestamp
    usage_limit INT Max uses allowed overall
    🔹 USER_COUPONS — Coupon usage logs to prevent abuse
    Attribute Data Type Description
    user_id INT (FK) Customer who used the coupon
    coupon_id INT (FK) Coupon used
    used_at TIMESTAMP Usage timestamp

    Business Rules:

    • Composite PK (user_id, coupon_id) — one use per user per coupon.
    🔹 WISHLISTS — User-created collection buckets
    Attribute Data Type Description
    wishlist_id INT (PK) Unique wishlist ID
    user_id INT (FK) Wishlist owner
    name VARCHAR(100) e.g. "Birthday Ideas", "Tech Gear"
    created_at TIMESTAMP Creation time
    🔹 WISHLIST_ITEMS — Product variants saved to wishlists
    Attribute Data Type Description
    wishlist_id INT (FK) Parent wishlist
    variant_id INT (FK) Saved product variant
    created_at TIMESTAMP Saved timestamp

    Business Rules:

    • Composite PK (wishlist_id, variant_id).
    🔹 RECOMMENDATIONS — Product suggestion cache entries
    Attribute Data Type Description
    recommendation_id INT (PK) Unique record ID
    user_id INT (FK) Target user
    variant_id INT (FK) Recommended product variant
    score DECIMAL(3,2) Relevance score (0.00 to 1.00)
    reason VARCHAR(255) e.g. "Based on your search for laptops"
    🔹 CUSTOMER_SUPPORT_TICKETS — Disputes, returns, and inquiries
    Attribute Data Type Description
    ticket_id INT (PK) Unique ticket ID
    user_id INT (FK) Customer who raised ticket
    order_id INT (FK) Related order (nullable)
    subject VARCHAR(200) Ticket subject
    description TEXT Detailed issue description
    status ENUM OPEN, ASSIGNED, RESOLVED, CLOSED
    created_at TIMESTAMP Created timestamp
    lock

    This content is reserved for Premium Members.

    Upgrade to Premium
    arrow_back Previous Topic Problem Statement Analysis Next Topic arrow_forward Relationships Explained

    Entity Details

    Create New Item
    help

    Submit Technical Query

    Have a question or run into an issue? Describe it below, upload an optional screenshot, and our engineering team will answer it!

    image Attach image (optional)

    Submit Feedback

    build Free Developer Utility Free Tool
    gavel

    Privacy & Legal Disclaimer

    1. Client-Side Browser Processing

    All utility tools on DeepEngineerHub (including Image to PDF, Text Formatters, JSON Converters, and Encryptors) execute 100% locally within your client browser using WebAssembly and JavaScript. No uploaded images, text, or documents are transmitted, collected, or stored on remote servers.

    2. Limitation of Liability ("As-Is" Provision)

    Tools and services are provided free of charge for convenience and educational purposes "as-is" without warranties of any kind. DeepEngineerHub shall not be held liable for any data loss, formatting inconsistencies, or indirect damages resulting from tool usage.

    3. Open Source & Third-Party Software

    Certain utilities utilize open-source client libraries (such as jsPDF, Mermaid.js, Pyodide) licensed under MIT, Apache, or BSD open licenses. All intellectual property remains with their respective copyright holders.