종합실습4
테이블 (14)
addresses
- address_id — BIGSERIAL — 기본 키 — NOT NULL
- customer_id — BIGINT — 외래 키 → customers.customer_id — NOT NULL
- line1 — TEXT — NOT NULL
- line2 — TEXT
- city — TEXT — NOT NULL
- state — TEXT
- postal_code — TEXT
- country_code — CHAR(2) — 외래 키 → country.country_code — NOT NULL
- is_default — BOOLEAN — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
categories
- category_id — BIGSERIAL — 기본 키 — NOT NULL
- parent_id — BIGINT — 외래 키 → categories.category_id
- category_name — TEXT — NOT NULL
country
- country_code — CHAR(2) — 기본 키 — NOT NULL
- country_name — TEXT — NOT NULL
customers
- customer_id — BIGSERIAL — 기본 키 — NOT NULL
- email — CITEXT — NOT NULL
- full_name — TEXT — NOT NULL
- phone — TEXT
- created_at — TIMESTAMPTZ — NOT NULL
- marketing_opt_in — BOOLEAN — NOT NULL
- country_code — CHAR(2) — 외래 키 → country.country_code
inventory
- product_id — BIGINT — 외래 키 → products.product_id — NOT NULL
- qty_on_hand — INT — NOT NULL
- reorder_point — INT — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
order_items
- order_item_id — BIGSERIAL — 기본 키 — NOT NULL
- order_id — BIGINT — 외래 키 → orders.order_id — NOT NULL
- product_id — BIGINT — 외래 키 → products.product_id — NOT NULL
- qty — INT — NOT NULL
- unit_price — NUMERIC(12,2) — NOT NULL
- discount — NUMERIC(12,2) — NOT NULL
- line_total — NUMERIC(12,2)
orders
- order_id — BIGSERIAL — 기본 키 — NOT NULL
- customer_id — BIGINT — 외래 키 → customers.customer_id — NOT NULL
- order_status — TEXT — NOT NULL
- order_ts — TIMESTAMPTZ — NOT NULL
- shipping_address_id — BIGINT — 외래 키 → addresses.address_id
- coupon_code — TEXT
- channel — TEXT — NOT NULL
payments
- payment_id — BIGSERIAL — 기본 키 — NOT NULL
- order_id — BIGINT — 외래 키 → orders.order_id — NOT NULL
- method — TEXT — NOT NULL
- amount — NUMERIC(12,2) — NOT NULL
- paid_at — TIMESTAMPTZ
product_prices
- price_id — BIGSERIAL — 기본 키 — NOT NULL
- product_id — BIGINT — 외래 키 → products.product_id — NOT NULL
- price — NUMERIC(12,2) — NOT NULL
- currency — CHAR(3) — NOT NULL
- valid_from — TIMESTAMPTZ — NOT NULL
- valid_to — TIMESTAMPTZ — NOT NULL
- is_current — BOOLEAN — NOT NULL
- EXCLUDE — USING
product_suppliers
- product_id — BIGINT — 외래 키 → products.product_id — NOT NULL
- supplier_id — BIGINT — 외래 키 → suppliers.supplier_id — NOT NULL
- primary_supplier — BOOLEAN — NOT NULL
products
- product_id — BIGSERIAL — 기본 키 — NOT NULL
- sku — TEXT — NOT NULL
- product_name — TEXT — NOT NULL
- category_id — BIGINT — 외래 키 → categories.category_id
- unit — TEXT — NOT NULL
- active — BOOLEAN — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
reviews
- review_id — BIGSERIAL — 기본 키 — NOT NULL
- product_id — BIGINT — 외래 키 → products.product_id — NOT NULL
- customer_id — BIGINT — 외래 키 → customers.customer_id — NOT NULL
- rating — INT — NOT NULL
- review_text — TEXT
- created_at — TIMESTAMPTZ — NOT NULL
- UNIQUE(product_id, — customer_id)
shipments
- shipment_id — BIGSERIAL — 기본 키 — NOT NULL
- order_id — BIGINT — 외래 키 → orders.order_id — NOT NULL
- carrier — TEXT — NOT NULL
- tracking_no — TEXT
- shipped_at — TIMESTAMPTZ
- delivered_at — TIMESTAMPTZ
suppliers
- supplier_id — BIGSERIAL — 기본 키 — NOT NULL
- supplier_name — TEXT — NOT NULL
- phone — TEXT