Ecommerce App Database Schema
1. User_roles
a. Id, primary key
b. Role_name (varchar) (Admin / Customer)
c. Created_at (timestamp)
d. Updated_at (timestamp) - NULL
2. Users
a. Id, Primary key
b. Role_id (admin/customer) (integer - foreign key)
c. Full name
d. Email
e. Password
f. Phone_number
g. Status (enum - active/inactive/block)
3. Categories
a. id , Primary key
b. Category_name
c. Url_slug (unique) (text) (cat-name)
d. Parent_cat_id, (integer)
e. Status (active / inactive)
4. Products
a. Id, Primary key
b. Product_name
c. Url_slug (unique)
d. Cat_id / category_id (integer)
e. Description (text)
f. Price (float - 10,2)
g. Stock_quantity (integer)
h. Status (active / inactive)
5. Product_variants
a. Id
b. Product_id
c. Color (optional)
d. Size (optional)
e. Price (float - 10,2)
f. Stock_quantity (integer)
6. Carts
a. Id, Primary key
b. User_id (foreign key - integer)
c. Product_id (foreign key - integer) - NULL
d. Product_variant_id (foreign key - integer) - NULL
e. Quantity (integer)
7. Shipping_addresses
a. Id, Primary key
b. User_id
c. Full_address
d. State
e. City
f. zip_code
8. Orders
a. Id
b. Order_number (unique number)
c. User_id
d. Total_amount (float) = 1500
e. Discount_amount (float) = 100
f. Gross_amount (float) = 1400
g. Shipping_amont (float) = 50
h. net _amount (float) = 1450
i. Status (enum - placed / processing / shipping / delivered)
j. Payment_status (enum - paid / not paid)
k. Payment_type (enum - netbanking / upi / cod)
l. Payment_transaction_id (varchar)
9. Order_items / order_products
a. Id, Primary key
b. Order_id (Foreign key - orders)
c. Product_id (Foreign key - products)
d. Product_variant_id (Foreign key - product_variants)
e. Product_name
f. Color (NULL)
g. Size (NULL)
h. Price (Float)
i. Quantity (integer)
j. Total_amount (Float)
10. Order_shipping_addresses
a. Id, primary key
b. Order_id (Foreign key - orders)
c. Shipping_address_id
d. Full_address
e. State
f. City
g. Zip_code
11. Wishlist
a. id , Primary key
b. User_id
c. Product_id
d. Product_variant_id
12. Offers / discounts
a. Id, Primary key
b. Coupon_code (varchar - unique)
c. Discount_type (enum - fixed / rate)
d. Disocunt_value (float)
e. Start_date (date)
f. End_date (date)
g. Description (text)
h. Status (active / inactive)
