wsd

Tables (20)

authors

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • bio — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

book_authors

  • id — BIGINT — Primary key — NOT NULL
  • book_id — BIGINT — Foreign key → books.id — NOT NULL — ON DELETE
  • author_id — BIGINT — Foreign key → authors.id — NOT NULL — ON DELETE

book_categories

  • id — BIGINT — Primary key — NOT NULL
  • category_id — BIGINT — Foreign key → categories.id — NOT NULL — ON DELETE
  • book_id — BIGINT — Foreign key → books.id — NOT NULL — ON DELETE

book_publisher

  • id — BIGINT — Primary key — NOT NULL
  • book_id — BIGINT — Foreign key → books.id — NOT NULL — ON DELETE
  • publisher_id — BIGINT — Foreign key → publishers.id — NOT NULL — ON DELETE

books

  • id — BIGINT — Primary key — NOT NULL
  • isbn13 — VARCHAR(20) — NOT NULL — UNIQUE
  • title — VARCHAR(255) — NOT NULL
  • subtitle — VARCHAR(255)
  • description — TEXT — NOT NULL
  • price — DECIMAL(12,2) — NOT NULL — CK(GOE 0)
  • stock_quantity — INT — NOT NULL — CK(GOE 0)
  • published_at — DATETIME
  • page_count — INT — CK(GOE 0)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

cart_items

  • id — BIGINT — Primary key — NOT NULL
  • cart_id — BIGINT — Foreign key → carts.id — NOT NULL — ON DELETE
  • book_id — BIGINT — Foreign key → books.id — NOT NULL
  • quantity — INT — NOT NULL — CK(BETWEEN 1 AND 100)
  • unit_price — DECIMAL(12,2) — NOT NULL — CK(GOE 0)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

carts

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • status — VARCHAR(20) — NOT NULL — ENUM
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

categories

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(150) — NOT NULL — UNIQUE
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

comment_likes

  • id — BIGINT — Primary key — NOT NULL
  • comment_id — BIGINT — Foreign key → comments.id — NOT NULL — ON DELETE
  • user_id — BIGINT — Foreign key → users.id — NOT NULL — ON DELETE
  • created_at — DATETIME — NOT NULL

comments

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL — ON DELETE
  • book_id — BIGINT — Foreign key → books.id — NOT NULL — ON DELETE
  • body — TEXT — NOT NULL
  • like_count — INT — NOT NULL — CK(GOE 0)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

favorites

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • book_id — BIGINT — Foreign key → books.id — NOT NULL
  • created_at — DATETIME — NOT NULL

order_items

  • id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.id — NOT NULL
  • book_id — BIGINT — Foreign key → books.id — NOT NULL
  • quantity — INT — NOT NULL — CK(BETWEEN 1 AND 100)
  • unit_price — BIGINT — NOT NULL — CK(GOE 0)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME — NOT NULL

orders

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • order_uuid — VARCHAR(40) — NOT NULL — UNIQUE
  • status — VARCHAR(20) — NOT NULL — ENUM
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • canceled_at — DATETIME

publishers

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(150) — NOT NULL — UNIQUE
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

review_likes

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL — ON DELETE
  • review_id — BIGINT — Foreign key → reviews.id — NOT NULL — ON DELETE
  • created_at — DATETIME — NOT NULL

reviews

  • id — BIGINT — Primary key — NOT NULL
  • book_id — BIGINT — Foreign key → books.id — NOT NULL — ON DELETE
  • user_id — BIGINT — Foreign key → users.id — NOT NULL — ON DELETE
  • rating — TINYINT — NOT NULL — CK(BETWEEN 0 ADN 5)
  • title — VARCHAR(255)
  • body — TEXT — NOT NULL
  • like_count — INT — NOT NULL — CK(GOE 0)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

top_reviews_cache

  • id — BIGINT — Primary key — NOT NULL
  • book_id — BIGINT — NOT NULL — UNIQUE
  • review_id — BIGINT — NOT NULL — UNIQUE
  • rank_no — INT — NOT NULL
  • like_count — INT — NOT NULL
  • rating — TINYINT — NOT NULL — CK(BETWEEN 0 ADN 5)
  • snapped_at — DATETIME — NOT NULL

user_refresh_token

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL — ON DELETE
  • refresh_token (refresh_token_hash) — VARCHAR(128) — NOT NULL — UNIQUE
  • expires_at — DATETIME — NOT NULL
  • revoked_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

users

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(320) — NOT NULL — UNIQUE
  • username — VARCHAR(50) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • hashed_password (password) — VARCHAR(100) — NOT NULL
  • role — VARCHAR(20) — NOT NULL — ENUM
  • status — VARCHAR(20) — NOT NULL — ENUM
  • address — VARCHAR(255)
  • phone_number — VARCHAR(30)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

wishlist_items

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • book_id — BIGINT — Foreign key → books.id — NOT NULL
  • created_at — DATETIME — NOT NULL