Project 1

## Your ecommerce database should store data for the following. Design your database however you wish, provided that these functionalities should be taken care of-

1. Coupon codes with discount rule and percentage per coupon
2. Order- shipments- invoices
3. User wishlist
4. Replacements catering to different orders- status
5. Reviews from customers for every product
6. Product- name, color, seller, sizes available, quantity available, cost, current discount, place of origin for delivery calculations, isFeatured, popularityIndex, avgratings, totalratings, reviews for ratings, review country, isOnSale, categories, brands, images, questions with answers about the products, description, tags, related products ids
7. Customer- gender, dob, city, pw, name, email, delivery addresses, contacts, current and prev orders, payment info saved
8. Orders- status- cancelled/completed/ordered/packed/shipped/delivered, deliveryexpectation/deliverydate, orderdate
9. Seller- Name, mobile no, password, categories of products provided

Answer:

Here is a possible database design for an e-commerce system that meets the requirements you have specified:

Coupons

• coupon_code (string): the code for the coupon
• discount_percentage (float): the percentage discount offered by the coupon
• discount_rule (string): the rule for applying the discount (e.g. "10% off all products" or "50% off first order")

Orders

• order_id (integer): a unique ID for the order
• customer_id (integer): the ID of the customer who placed the order
• order_status (string): the current status of the order (e.g. "cancelled", "completed", etc.)
• order_date (datetime): the date and time the order was placed
• delivery_expectation (datetime): the expected delivery date for the order
• delivery_date (datetime): the actual delivery date for the order

Invoices

• invoice_id (integer): a unique ID for the invoice
• order_id (integer): the ID of the order for which the invoice was generated

Shipments

• shipment_id (integer): a unique ID for the shipment
• order_id (integer): the ID of the order for which the shipment was created
• tracking_number (string): the tracking number for the shipment

User Wishlist

• wishlist_id (integer): a unique ID for the wishlist
• customer_id (integer): the ID of the customer who owns the wishlist

Replacements

• replacement_id (integer): a unique ID for the replacement request
• order_id (integer): the ID of the order for which the replacement is being requested
• replacement_status (string): the current status of the replacement request (e.g. "pending", "approved", etc.)

Reviews

• review_id (integer): a unique ID for the review
• product_id (integer): the ID of the product being reviewed
• customer_id (integer): the ID of the customer who wrote the review
• rating (integer): the rating given by the customer (e.g. 1-5)
• review_text (string): the text of the review
• review_country (string): the country from which the review was submitted

Products

• product_id (integer): a unique ID for the product
• name (string): the name of the product
• color (string): the color of the product
• seller_id (integer): the ID of the seller who is selling the product
• sizes_available (string): a comma-separated list of the sizes available for the product
• quantity_available (integer): the number of units of the product currently in stock
• cost (float): the cost of the product
• current_discount (float): the current discount being offered on the product
• place_of_origin (string): the place of origin for the product, used for delivery calculations
• is_featured (boolean): whether the product is currently being featured on the website
• popularity_index (integer): a measure of the popularity of the product among customers
• avg_ratings (float): the average rating for the product, based on customer reviews
• total_ratings (integer): the total number of ratings for the product
• reviews (text): a JSON object containing the individual reviews for the product, including the customer's name, rating, and review text

[ER Diagram]

And here are the corresponding tables for the database:

Coupon:

• coupon_id (PK)
• discount_rule
• percentage

Order:

• order_id (PK)
• status
• delivery_expectation
• delivery_date
• order_date

Shipment:

• shipment_id (PK)
• order_id (FK)
• invoice_id (FK)

Invoice:

• invoice_id (PK)
• order_id (FK)

User_Wishlist:

• user_id (FK)
• product_id (FK)

Replacement:

• replacement_id (PK)
• order_id (FK)
• status

Review:

• review_id (PK)
• product_id (FK)
• customer_id (FK)
• rating
• review_country
• review_text

Product:

• product_id (PK)
• name
• color
• seller_id (FK)
• size
• quantity
• cost
• current_discount
• place_of_origin
• is_featured
• popularity_index
• avg_ratings
• total_ratings
• review_ids (FK)
• is_on_sale
• categories
• brands
• images
• description
• tags
• related_product_ids (FK)

Customer:

• customer_id (PK)
• gender
• dob
• city
• password
• name
• email
• delivery_addresses
• contacts
• current_orders (FK)
• prev_orders (FK)
• payment_info

Seller:

• seller_id (PK)
• name
• mobile_no
• password
• categories

Here is a database design for an ecommerce website:

QUERY:

SELECT coupon_code, discount_rule, percentage FROM coupons;

SELECT order_id, shipment_id, invoice_id FROM orders;

SELECT user_id, product_id FROM wishlist;

SELECT replacement_id, order_id, status FROM replacements;

SELECT product_id, customer_id, review, rating FROM reviews;

SELECT product_id, name, color, seller_id, sizes_available, quantity_available, cost, current_discount, place_of_origin, isFeatured, popularityIndex, avgratings, totalratings, review_country, isOnSale, categories, brands, images, questions, answers, description, tags, related_products_ids FROM products;

SELECT customer_id, gender, dob, city, password, name, email, delivery_addresses, contacts, current_orders, prev_orders, payment_info FROM customers;

SELECT order_id, status, delivery_expectation, delivery_date, order_date FROM orders;

SELECT seller_id, name, mobile_no, password, categories FROM sellers;

Coupon codes table:

coupon_id	code	discount_rule	percentage
1	10OFF	orders over $50	10%
2	20OFF	first-time customers	20%

Order table:

order_id	customer_id	order_status	delivery_expectation	delivery_date	order_date
1	1	ordered	2-3 business days	2021-05-14	2021-05-12
2	2	shipped	3-5 business days	2021-05-18	2021-05-15

Shipment table:

shipment_id	order_id	shipment_status
1	1	packed
2	2	shipped

Invoice table:

invoice_id	order_id	amount
1	1	$50.00
2	2	$75.00

User wishlist table:

wishlist_id	customer_id	product_id
1	1	10
2	2	15

Replacement table:

replacement_id	order_id	product_id	status
1	1	10	approved
2	2	15	denied

Reviews table:

review_id	customer_id	product_id	rating	review
1	1	10	4	Great product!
2	2	15	3	Good product, but not as described

Product table:

product_id	name	color	seller_id	sizes	quantity	cost	current_discount	place_of_origin	isFeatured	popularityIndex	avgrating	totalratings	reviews	review_country	isOnSale	categories	brands	images	questions	answers	description	tags	related_products
10	Shirt	blue	1	S, M, L	50	$20	10%	USA	1	100	4.5	50	50	USA	1	Clothing	Nike	img1, img2	q1, q2	a1, a2	Great shirt!	casual	15, 20

Customer Table:

Customer ID	Gender	DOB	City	Password	Name	Email	Delivery Address	Contact	Current Order	Prev Order	Payment Info
1	Male	01/01/1995	New York	abc123	John Smith	[email protected]	123 Main St, NY	555-555-1212	2	5,7,9	Visa
2	Female	02/14/1985	Los Angeles	xyz789	Jane Doe	[email protected]	456 Sunset Blvd, LA	555-555-1213	3	1,6,8	Mastercard

Orders Table:

Order ID	Customer ID	Status	Delivery Expectation	Delivery Date	Order Date
1	2	Ordered	02/01/2021	NULL	01/25/2021
2	1	Packed	01/29/2021	NULL	01/24/2021
3	2	Shipped	01/30/2021	NULL	01/26/2021
4	1	Delivered	NULL	01/29/2021	01/24/2021
5	2	Completed	NULL	02/01/2021	01/25/2021

Seller Table:

Seller ID	Name	Mobile No	Password	Product Categories
1	Bob	555-555-1214	def456	Clothing, Shoes
2	Alice	555-555-1215	ghi789	Electronics, Toys