Spartans Market Town | Database Design

# Database Design

This repository contains the database design for a system that manages users, addresses, stores, products, orders, carts, payments, and ordered products. This design outlines the tables, their relationships, and the attributes within each table.

## Tables

### User Table

- **user_id (Primary Key):** Unique identifier for each user.
- **first_name:** First name of the user.
- **last_name:** Last name of the user.
- **mobile_number:** User's mobile number.
- **email:** User's email address.
- **password:** User's password (hashed and secure).

### Address

- **user_id (Foreign Key):** Relates to the user to whom the address belongs.
- **address:** User's street address.
- **pincode:** Postal code or ZIP code.
- **state:** State or province.
- **districts:** District or locality within the state.

### Store

- **store_id (Primary Key):** Unique identifier for each store.
- **store_name:** Name of the store.
- **store_logo:** Logo of the store.
- **category:** Category or type of products the store sells.

### Products

- **product_id (Primary Key):** Unique identifier for each product.
- **product_img:** Image or URL of the product.
- **product_title:** Title or name of the product.
- **product_price:** Price of the product.
- **store_id (Foreign Key):** Relates to the store that sells the product.

### Order

- **order_id (Primary Key):** Unique identifier for each order.
- **user_id (Foreign Key):** Relates to the user who placed the order.
- **payment_status:** Status of payment for the order (e.g., paid, pending).
- **totalPrice:** Total price of the order.
- **order_date:** Date when the order was placed.

### Cart

- **number_of_product:** Number of different products in the cart.
- **Total_items:** Total number of items in the cart.
- **Quantity:** Quantity of each product in the cart.

### Payment

- **payment_id (Primary Key):** Unique identifier for each payment.
- **payment_option:** Payment option used (e.g., credit card, PayPal).
- **card_number:** Credit card number (masked or encrypted for security).
- **card_holder:** Name of the cardholder.
- **expiry_date:** Expiry date of the payment card.
- **cvc:** Card Verification Code (CVC) for card security.

### Ordered_Products

- **ordered_product_id (Primary Key):** Unique identifier for each ordered product.
- **order_id (Foreign Key):** Relates to the order in which the product was purchased.
- **product_id (Foreign Key):** Relates to the product that was ordered.
- **quantity:** Quantity of the ordered product.

## Database Relationships

- Each user can have multiple addresses, so the `user_id` in the `Address` table is a foreign key referencing the `user_id` in the `User Table`.
- Each store can have multiple products, so the `store_id` in the `Products` table is a foreign key referencing the `store_id` in the `Store` table.
- Each order can contain multiple ordered products, so the `order_id` in the `Ordered_Products` table is a foreign key referencing the `order_id` in the `Order` table, and the `product_id` in the `Ordered_Products` table is a foreign key referencing the `product_id` in the `Products` table.
- Each order is associated with a user, so the `user_id` in the `Order` table is a foreign key referencing the `user_id` in the `User Table`.
- Each order is associated with a payment, so the `payment_id` in the `Payment` table is a foreign key referencing the `order_id` in the `Order` table.

This database design provides a structured framework for managing users, addresses, stores, products, orders, carts, payments, and ordered products within your system.

## Related Resources
- [Schema SQL File](https://github.com/fssa-batch3/sec_c_mathankumar.nagarajan__corejava_project_2/blob/main/src/main/sql/sqlscripts.sql)