Vendor

email: VARCHAR, PRIMARY KEY, UNIQUE, Vendor's email address

id: INT, AUTO_INCREMENT, UNIQUE, Vendor ID

profileImgUrl: VARCHAR, NULLABLE, URL of the vendor's profile image

name: VARCHAR, NOT NULL, Vendor's name

password: VARCHAR, NOT NULL, Vendor's password (hashed)

shop_address: TEXT, NOT NULL, Vendor's shop address

shop_description: TEXT, NULLABLE, Description of the shop

shop_mobile: VARCHAR, NOT NULL, UNIQUE, Vendor's shop mobile number

shop_name: VARCHAR, NOT NULL, UNIQUE, Vendor's shop name

shop_website: VARCHAR, NULLABLE, UNIQUE, Shop's website URL

User

email: VARCHAR, PRIMARY KEY, UNIQUE, User's email address

id: INT, AUTO_INCREMENT, UNIQUE, User ID

imgurl: VARCHAR, NULLABLE, URL of user's profile image

name: VARCHAR, NOT NULL, User's name

Product

featured_id: INT, NULLABLE, ID for featured products

is_featured: BOOLEAN, DEFAULT FALSE, Whether the product is featured

p_category: VARCHAR, NOT NULL, Product category

p_colors: JSON, NULLABLE, Array of color objects

color_code: VARCHAR, NULLABLE, Hex color code for the product

p_description: TEXT, NOT NULL, Product description

p_images: JSON, NOT NULL, Array of image URLs

p_name: VARCHAR, NOT NULL, UNIQUE, Product name

p_price: DECIMAL(10,2), NOT NULL, Product price

p_quantity: INT, NOT NULL, Available product quantity

p_rating: DECIMAL(2,1), DEFAULT 0, Average product rating (1-5)

p_seller: INT, FOREIGN KEY REFERENCES Vendor(id), Vendor ID selling the product

p_subcategory: VARCHAR, NULLABLE, Product subcategory

p_wishlist: JSON, NULLABLE, Array of users who wishlisted it

id: INT, FOREIGN KEY REFERENCES User(id), User ID for wishlist

vendor_id: INT, FOREIGN KEY REFERENCES Vendor(id), Vendor ID

Cart

added_by: INT, FOREIGN KEY REFERENCES User(id), ID of the user who added the item

color: VARCHAR, NOT NULL, Color selected for the product

img: VARCHAR, NOT NULL, Image URL of the product

qty: INT, DEFAULT 1, Quantity of the product

Vendor name: VARCHAR, FOREIGN KEY REFERENCES Vendor(name), Name of the vendor

title: VARCHAR, NOT NULL, Product title

tprice: DECIMAL(10,2), NOT NULL, Total price of the cart item

vendor_id: INT, FOREIGN KEY REFERENCES Vendor(id), Vendor ID

Order

order_by: INT, FOREIGN KEY REFERENCES User(id), ID of the user who placed order

order_by_address: TEXT, NOT NULL, Address of the customer

order_by_city: VARCHAR, NOT NULL, City of the customer

order_by_email: VARCHAR, NOT NULL, FOREIGN KEY REFERENCES User(email), Email of the

customer
order_by_name: VARCHAR, NOT NULL, Name of the customer

order_by_phone: VARCHAR, NOT NULL, UNIQUE, Phone number of the customer

order_by_postalcode: VARCHAR, NOT NULL, Postal code of the customer

order_by_state: VARCHAR, NOT NULL, State of the customer

order_code: VARCHAR, PRIMARY KEY, UNIQUE, Unique code for the order

order_confirmed: BOOLEAN, DEFAULT FALSE, Whether the order is confirmed

order_date: TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, Date of the order

order_delivered: BOOLEAN, DEFAULT FALSE, Whether the order is delivered

order_on_delivery: BOOLEAN, DEFAULT FALSE, Whether the order is COD

order_placed: BOOLEAN, DEFAULT TRUE, Whether the order is placed

orders: JSON, NOT NULL, Array of product objects

color: VARCHAR, NULLABLE, Selected color of the product

img: VARCHAR, NULLABLE, Product image URL

qty: INT, NOT NULL, Quantity of the product

title: VARCHAR, NOT NULL, Title of the product

tprice: DECIMAL(10,2), NOT NULL, Total price for the product

vendor_id: INT, FOREIGN KEY REFERENCES Vendor(id), Vendor ID

payment_method: VARCHAR, NOT NULL, Method of payment

shipping_method: VARCHAR, NOT NULL, Shipping method selected

total_amount: DECIMAL(10,2), NOT NULL, Total amount of the order

Chat

created_on: TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, Chat creation timestamp

friend_name: VARCHAR, NOT NULL, Name of the friend in chat

fromId: INT, FOREIGN KEY REFERENCES User(id), Sender's User ID

last_msg: TEXT, NOT NULL, Last message in the chat

sender_name: VARCHAR, NOT NULL, Name of the sender

users: JSON, NOT NULL, Array of users in the chat

id: INT, FOREIGN KEY REFERENCES User(id), User ID

Message

created_on: TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, Message creation timestamp

msg: TEXT, NOT NULL, Content of the message

uid: INT, FOREIGN KEY REFERENCES User(id), User ID of the sender

Review & Rating

review_id: INT, PRIMARY KEY, AUTO_INCREMENT, Unique review ID

customer_id: INT, FOREIGN KEY REFERENCES User(id), Customer/User ID who reviewed

product_id: INT, FOREIGN KEY REFERENCES Product(featured_id), Product ID being reviewed

rating: DECIMAL(2,1), NOT NULL, Rating given (1-5)

review_text: TEXT, NULLABLE, Text content of the review

created_at: TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, Review creation timestamp

Payment

payment_id: INT, PRIMARY KEY, AUTO_INCREMENT, Unique Payment ID

order_id: VARCHAR, FOREIGN KEY REFERENCES Order(order_code), Order ID associated with

payment

customer_id: INT, FOREIGN KEY REFERENCES User(id), Customer/User ID who paid

amount_paid: DECIMAL(10,2), NOT NULL, Amount paid in the transaction

payment_method: VARCHAR, NOT NULL, Payment method used

transaction_id: VARCHAR, UNIQUE, NOT NULL, Unique Transaction ID

payment_status: VARCHAR, NOT NULL, Payment status (e.g., Paid/Failed)

created_at: TIMESTAMP, DEFAULT CURRENT_TIMESTAMP, Payment creation timestamp