mini-amazon-316

Tables (7)

CartItem (cartItem entity)

  • id (ownerId, productId, sellerId) — INT — Primary key — NOT NULL
  • ownerId (user id of cart owner) — INT — Foreign key → User.id — NOT NULL
  • productId (product id of cart item row) — INT — Foreign key → Product.id — NOT NULL
  • quantity (quantity added) — INT
  • orderId (order id) — INT — Foreign key → Order.id — NOT NULL
  • isOrdered (boolean indicating order status) — TINYINT(1)
  • isFulfilled (boolean indicating fulfill status) — TINYINT(1)
  • sellerId (specific seller of cart item) — INT — Foreign key → User.id — NOT NULL
  • fulfilledAt (timestamp when fulfilled) — DATE
  • savedStatus (boolean indicating saved status) — TINYINT(1)

Inventory (inventory entity)

  • id (combination of sellerId and productId) — INT — Primary key — NOT NULL
  • quantity (seller stock) — INT
  • sellerId (user id of seller) — INT — Foreign key → User.id — NOT NULL
  • productId (product id in stock) — INT — Foreign key → Product.id — NOT NULL

Order (order entity)

  • id (system-assigned unique id) — INT — Primary key — NOT NULL
  • isFulfilled (boolean indicating fulfill status) — TINYINT(1)
  • createdAt (time of order) — DATE

Product (product entity)

  • id (system-assigned, unique id) — INT — Primary key — NOT NULL
  • name (short product name) — VARCHAR(255)
  • description (limit on length) — VARCHAR(255)
  • price (price of product) — DECIMAL(12,2)
  • available (boolean indicating availability) — TINYINT(1)
  • category (limited book category) — ENUM
  • createdAt (time of product upload) — DATE
  • imageSrc (url of image) — VARCHAR(255)
  • viewCount (number of product views) — INT
  • avgRating (average of product ratings) — DECIMAL(12,2)
  • registeredBy (user who registered product) — INT — Foreign key → User.id — NOT NULL

Product Review (productReview entity)

  • id (system-assigned, unique id) — INT — Primary key — NOT NULL
  • createdAt (time of review) — DATE
  • rating (range restriction from 1 to 5) — INT
  • description (text content of rating) — VARCHAR(255)
  • productId (product id associated to review) — INT — Foreign key → Product.id — NOT NULL
  • writerId (user id who wrote review) — INT — Foreign key → User.id — NOT NULL
  • upvote (number of product review upvotes) — INT
  • downvote (number of product review downvotes) — INT

Seller Review (sellerReview entity)

  • id (system-assigned, unique id) — INT — Primary key — NOT NULL
  • createdAt (time of review) — DATE
  • rating (range restriction from 1 to 5) — INT
  • description (text content of rating) — VARCHAR(255)
  • sellerId (seller id associated to review) — INT — Foreign key → User.id — NOT NULL
  • writerId (user id who wrote review) — INT — Foreign key → User.id — NOT NULL
  • upvote (number of seller review upvotes) — INT
  • downvote (number of seller review downvotes) — INT

User (user entity)

  • id (system-assigned, unique id) — INT — Primary key — NOT NULL
  • email (unique user email) — VARCHAR(255)
  • password (protected password) — VARCHAR(255)
  • firstname (firstname of user) — VARCHAR(255)
  • lastname (lastname of user) — VARCHAR(255)
  • address (shipping and billing address) — VARCHAR(255)
  • balance (amount of money) — FLOAT
  • isSeller (indicator for seller) — TINYINT(1)
  • avgRating (average review rating for sellers) — DECIMAL(12,2)