// ============================================ // Amazon Data Model — Complete DBML Schema // 25 Entities | FAANG Interview Level // ============================================

// ---- ENUMS ----

Enum order_status { PENDING PAID SHIPPED DELIVERED CANCELLED }

Enum shipment_status { IN_TRANSIT OUT_FOR_DELIVERY DELIVERED RETURNED EXCEPTION }

Enum discount_type { PERCENTAGE FLAT }

Enum support_status { OPEN ASSIGNED RESOLVED CLOSED }

// ---- TABLES ----

Table users { user_id int [pk, increment] email varchar(255) [unique, not null] phone_number varchar(20) [unique] password_hash varchar(255) [not null] full_name varchar(100) [not null] is_prime boolean [default: false] prime_joined_at timestamp created_at timestamp [default: now()] updated_at timestamp

Note: 'Customer accounts.' }

Table shipping_addresses { address_id int [pk, increment] user_id int [ref: > users.user_id, not null] address_line1 varchar(150) [not null] address_line2 varchar(150) city varchar(100) [not null] state varchar(100) [not null] postal_code varchar(20) [not null] country varchar(100) [not null] is_default boolean [default: false] }

Table payment_methods { payment_method_id int [pk, increment] user_id int [ref: > users.user_id, not null] provider varchar(50) [not null] // e.g., VISA, Mastercard, PayPal tokenized_card varchar(255) [not null] // Storing token, never raw card info expiration_date date [not null] is_default boolean [default: false] }

Table sellers { seller_id int [pk, increment] business_name varchar(150) [unique, not null] contact_email varchar(255) [not null] tax_id varchar(50) [unique, not null] rating decimal(3,2) [default: 0.00] is_verified boolean [default: false] created_at timestamp [default: now()] }

Table categories { category_id int [pk, increment] parent_category_id int [ref: > categories.category_id, null] name varchar(100) [not null] description text }

Table products { product_id int [pk, increment] seller_id int [ref: > sellers.seller_id, not null] category_id int [ref: > categories.category_id, not null] title varchar(255) [not null] description text brand varchar(100) created_at timestamp [default: now()] }

Table product_variants { variant_id int [pk, increment] product_id int [ref: > products.product_id, not null] sku varchar(100) [unique, not null] price decimal(10,2) [not null] attributes jsonb [not null] // e.g., {"color": "black", "size": "L"} weight_kg decimal(6,2) created_at timestamp [default: now()] }

Table product_media { media_id int [pk, increment] product_id int [ref: > products.product_id, not null] media_url varchar(512) [not null] position int [default: 0] }

Table fulfillment_centers { center_id int [pk, increment] name varchar(100) [not null] location_code varchar(20) [unique, not null] address varchar(255) [not null] }

Table inventories { inventory_id int [pk, increment] variant_id int [ref: > product_variants.variant_id, not null] center_id int [ref: > fulfillment_centers.center_id, not null] quantity_available int [default: 0] quantity_reserved int [default: 0]

indexes { (variant_id, center_id) [unique] } }

Table carts { cart_id int [pk, increment] user_id int [ref: - users.user_id, unique, not null] created_at timestamp [default: now()] updated_at timestamp }

Table cart_items { cart_item_id int [pk, increment] cart_id int [ref: > carts.cart_id, not null] variant_id int [ref: > product_variants.variant_id, not null] quantity int [default: 1] created_at timestamp [default: now()]

indexes { (cart_id, variant_id) [unique] } }

Table orders { order_id int [pk, increment] user_id int [ref: > users.user_id, not null] shipping_address_id int [ref: > shipping_addresses.address_id, not null] status order_status [default: 'PENDING'] total_amount decimal(12,2) [not null] tax_amount decimal(10,2) [default: 0.00] shipping_cost decimal(8,2) [default: 0.00] created_at timestamp [default: now()] }

Table order_items { order_item_id int [pk, increment] order_id int [ref: > orders.order_id, not null] variant_id int [ref: > product_variants.variant_id, not null] quantity int [not null] price_per_unit decimal(10,2) [not null] // Price snapshot }

Table payments { payment_id int [pk, increment] order_id int [ref: - orders.order_id, unique, not null] payment_method_id int [ref: > payment_methods.payment_method_id, not null] transaction_reference varchar(255) [unique, not null] amount decimal(12,2) [not null] status varchar(50) [not null] // SUCCESS, FAILED, REFUNDED processed_at timestamp [default: now()] }

Table order_shipments { shipment_id int [pk, increment] order_id int [ref: > orders.order_id, not null] carrier varchar(50) [not null] // FedEx, DHL, USPS, Amazon Logistics tracking_number varchar(100) [unique, not null] status shipment_status [default: 'IN_TRANSIT'] estimated_delivery date shipped_at timestamp }

Table shipment_tracking_logs { log_id int [pk, increment] shipment_id int [ref: > order_shipments.shipment_id, not null] location varchar(150) [not null] activity_description varchar(255) [not null] logged_at timestamp [default: now()] }

Table reviews { review_id int [pk, increment] product_id int [ref: > products.product_id, not null] user_id int [ref: > users.user_id, not null] rating int [not null] // 1 to 5 stars headline varchar(150) comment text verified_purchase boolean [default: false] created_at timestamp [default: now()] }

Table review_helpful_votes { review_id int [ref: > reviews.review_id, not null] user_id int [ref: > users.user_id, not null] created_at timestamp [default: now()]

indexes { (review_id, user_id) [pk] } }

Table coupons { coupon_id int [pk, increment] code varchar(50) [unique, not null] discount_type discount_type [not null] value decimal(10,2) [not null] max_discount decimal(10,2) start_date timestamp [not null] end_date timestamp [not null] usage_limit int }

Table user_coupons { user_id int [ref: > users.user_id, not null] coupon_id int [ref: > coupons.coupon_id, not null] used_at timestamp [default: now()]

indexes { (user_id, coupon_id) [pk] } }

Table wishlists { wishlist_id int [pk, increment] user_id int [ref: > users.user_id, not null] name varchar(100) [default: 'Wish List'] created_at timestamp [default: now()] }

Table wishlist_items { wishlist_id int [ref: > wishlists.wishlist_id, not null] variant_id int [ref: > product_variants.variant_id, not null] created_at timestamp [default: now()]

indexes { (wishlist_id, variant_id) [pk] } }

Table recommendations { recommendation_id int [pk, increment] user_id int [ref: > users.user_id, not null] variant_id int [ref: > product_variants.variant_id, not null] score decimal(3,2) [not null] reason varchar(255) // e.g., 'Based on your search for laptops' }

Table customer_support_tickets { ticket_id int [pk, increment] user_id int [ref: > users.user_id, not null] order_id int [ref: > orders.order_id] subject varchar(200) [not null] description text [not null] status support_status [default: 'OPEN'] created_at timestamp [default: now()] }

// ---- TABLE GROUPS ----

TableGroup user_payment_domain { users shipping_addresses payment_methods wishlists wishlist_items }

TableGroup catalog_domain { categories products product_variants product_media sellers }

TableGroup inventory_logistics_domain { fulfillment_centers inventories order_shipments shipment_tracking_logs }

TableGroup cart_order_domain { carts cart_items orders order_items payments }

TableGroup feedback_support_domain { reviews review_helpful_votes coupons user_coupons recommendations customer_support_tickets }

This content is reserved for Premium Members.