Core: Define UML Diagram

[kirubeltadesse]

Description

The is part of the project structure and we need to define the:

• Database Schema
• UML Diagram
• Sequence Diagram

Acceptable criteria

We need to have a mermaid diagram representation for the feature work.

Related strory

This is a research and documentation story.

[kirubeltadesse]

1. Users Table:

UserID (Primary Key)
Username
Password (hashed and salted)
Email
First Name
Last Name
Address
Phone Number
Other relevant user information

• Products Table:

ProductID (Primary Key)
Product Name
Description
Price
Stock Quantity
Category (or CategoryID as a foreign key to a Category table)
Image URL
Other product details

• Categories Table:

CategoryID (Primary Key)
Category Name
Description
Orders Table:

OrderID (Primary Key)
UserID (Foreign Key to Users Table)
Order Date
Total Amount
Order Status (e.g., processing, shipped, delivered)

• OrderItems Table:

OrderItemID (Primary Key)
OrderID (Foreign Key to Orders Table)
ProductID (Foreign Key to Products Table)
Quantity
Subtotal (Price x Quantity)

• Reviews Table:

ReviewID (Primary Key)
ProductID (Foreign Key to Products Table)
UserID (Foreign Key to Users Table)
Rating
Review Text
Review Date
ShoppingCart Table (for tracking items in a user's cart before checkout):

CartID (Primary Key)
UserID (Foreign Key to Users Table)
ProductID (Foreign Key to Products Table)
Quantity

• Payment Methods Table:

PaymentMethodID (Primary Key)
UserID (Foreign Key to Users Table)
Payment Method Type (e.g., credit card, PayPal)
Payment Method Details (e.g., card number, PayPal email)

• Shipping Addresses Table:

AddressID (Primary Key)
UserID (Foreign Key to Users Table)
Recipient Name
Address
City
State/Province
ZIP/Postal Code
Country

---

Tables:

a. Customers:

customer_id (Primary Key)
first_name
last_name
email
phone_number
created_at
b. Products:

product_id (Primary Key)
name
description
price
stock_quantity
created_at
c. Categories:

category_id (Primary Key)
name
d. Product_Category (Many-to-Many Junction Table):

product_id (Foreign Key referencing Products)
category_id (Foreign Key referencing Categories)
e. Orders:

order_id (Primary Key)
customer_id (Foreign Key referencing Customers)
order_date
total_amount
f. Order_Items:

order_item_id (Primary Key)
order_id (Foreign Key referencing Orders)
product_id (Foreign Key referencing Products)
quantity
subtotal