Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
**Customers Table**:
– `customer_id` (Primary Key)
– `first_name`
– `last_name`
– `email`
– `phone`
– `address`
– `city`
– `state`
– `zip_code`
– `registration_date`
2. **Products Table**:
– `product_id` (Primary Key)
– `product_name`
– `description`
– `price`
– `stock_quantity`
– `category_id` (Foreign Key to Categories Table)
3. **Categories Table**:
– `category_id` (Primary Key)
– `category_name`
4. **Orders Table**:
– `order_id` (Primary Key)
– `customer_id` (Foreign Key to Customers Table)
– `order_date`
– `total_amount`
– `status` (e.g., 'Processing’, 'Shipped’, 'Delivered’)
5. **Order_Items Table**:
– `order_item_id` (Primary Key)
– `order_id` (Foreign Key to Orders Table)
– `product_id` (Foreign Key to Products Table)
– `quantity`
– `item_price`
– `subtotal`
6. **Reviews Table**:
– `review_id` (Primary Key)
– `product_id` (Foreign Key to Products Table)
– `customer_id` (Foreign Key to Customers Table)
– `rating` (e.g., 1 to 5 stars)
– `comment`
– `review_date`
– Customers can register with their personal information.
– Products are categorized, and each product has details like name, description, price, and available stock.
– Orders are placed by customers, which include information about the customer, order date, total amount, and status.
– Each order can have multiple items, which are recorded in the `Order_Items` table.
– Customers can leave reviews for products they have purchased.
– You can include tables for product images, product attributes (e.g., size, color), and discounts if your store requires these features.
– Implement appropriate indexes and constraints to ensure data integrity and optimize query performance.
– For security, consider encrypting sensitive customer information and protecting the database from unauthorized access.
This is a basic starting point for a merchandise store database schema, and you can expand it further based on the specific needs and features of your online store.