Database Schema

[Alaalser]

Database Schema

Create the schema of the database, and think about the advantages and disadvantages of everything you do. Think also about the future changes or features that could be required.

Draw the database schema with relations between tables using a drawing tool (draw.io). Share it with the team and take their opinion about it.

[ammarhararah]

Suggested Schema:

https://drive.google.com/file/d/1rxTGoM5Auktv2UGCVkIgB0yLPNldSVHA/view?usp=drive_link

Categories +-------------+--------------+ | category_id | name | +-------------+--------------+ | PK | | +-------------+--------------+

Brands +----------+-------+ | brand_id | name | +----------+-------+ | PK | | +----------+-------+

Products +-------------+---------+--------------+-----------+------------------+-----------------+ | product_id | name | description | price | stock_quantity | discount_percentage | rating_average | brand_id (FK) | category_id (FK) | +-------------+---------+--------------+-----------+------------------+-----------------+ | PK | | | | | | | | | +-------------+---------+--------------+-----------+------------------+-----------------+

Ratings +-------------+--------------+---------+----------------+ | rating_id | product_id | user_id | rating_value | +-------------+--------------+---------+----------------+ | PK | FK | | | +-------------+--------------+---------+----------------+

Reviews +-------------+--------------+---------+--------------+---------------------+ | review_id | product_id | user_id | review_text | timestamp | +-------------+--------------+---------+--------------+---------------------+ | PK | FK | | | | +-------------+--------------+---------+--------------+---------------------+

Cart +----------+---------+-------------+---------+ | cart_id | user_id | product_id | quantity| +----------+---------+-------------+---------+ | PK | | FK | | +----------+---------+-------------+---------+

Wishlist +--------------+---------+-------------+ | wishlist_id | user_id | product_id | +--------------+---------+-------------+ | PK | | FK | +--------------+---------+-------------+

PaymentGateways +---------------+---------+--------------+ | gateway_id | name | description | +---------------+---------+--------------+ | PK | | | +---------------+---------+--------------+

Filters +------------+---------+--------------+ | filter_id | name | description | +------------+---------+--------------+ | PK | | | +------------+---------+--------------+

[ammarhararah]

Table: Category

• category_id (Primary Key)
• name

Table: Brand

• brand_id (Primary Key)
• name

Table: Product

• product_id (Primary Key)
• name
• description
• price
• stock_quantity
• discount_percentage
• rating_average
• brand_id (Foreign Key references Brands)
• category_id (Foreign Key references Categories)

Table: Rating

• rating_id (Primary Key)
• product_id (Foreign Key references Products)
• user_id
• rating_value (e.g., 1 to 5)

Table: Reviews

• review_id (Primary Key)
• product_id (Foreign Key references Products)
• user_id
• review_text
• timestamp

Table: Cart

• cart_id (Primary Key)
• user_id
• product_id (Foreign Key references Products)
• quantity

Table: Wishlist

• wishlist_id (Primary Key)
• user_id
• product_id (Foreign Key references Products)

Table: PaymentGateways

• gateway_id (Primary Key)
• name
• description

Table: Filters

• filter_id (Primary Key)
• name
• description