Entity Relationship Diagram Example for a E-commerce System

Entities and Relationships:

1. Entity: User
   • Attributes:
     • UserID (Primary Key)
     • FirstName
     • LastName
     • Email (Unique)
     • Password
     • CreatedAt
   • Relationships:
     • A user can have many orders (One-to-Many relationship with the Order entity).
2. Entity: Product
   • Attributes:
     • ProductID (Primary Key)
     • ProductName
     • Description
     • Price
     • StockQuantity
     • CategoryID (Foreign Key)
   • Relationships:
     • A product belongs to one category (Many-to-One relationship with the Category entity).
     • A product can be included in many order items (Many-to-Many relationship with the Order entity through the OrderItem entity).
3. Entity: Category
   • Attributes:
     • CategoryID (Primary Key)
     • CategoryName
   • Relationships:
     • A category can have many products (One-to-Many relationship with the Product entity).
4. Entity: Order
   • Attributes:
     • OrderID (Primary Key)
     • UserID (Foreign Key)
     • OrderDate
     • TotalAmount
     • Status
   • Relationships:
     • An order belongs to one user (Many-to-One relationship with the User entity).
     • An order can contain multiple products (Many-to-Many relationship with the Product entity through the OrderItem entity).
5. Entity: OrderItem
   • Attributes:
     • OrderItemID (Primary Key)
     • OrderID (Foreign Key)
     • ProductID (Foreign Key)
     • Quantity
     • UnitPrice
   • Relationships:
     • An order item belongs to one order (Many-to-One relationship with the Order entity).
     • An order item refers to one product (Many-to-One relationship with the Product entity).

---

Diagram Layout Description

1. User Entity: Positioned at the top-left of the diagram, connected to the Order entity with a “One-to-Many” relationship.
2. Order Entity: Positioned in the center, connected to the OrderItem entity with a “One-to-Many” relationship and to the User entity.
3. OrderItem Entity: Positioned below the Order entity, connecting to both Order and Product entities with “Many-to-One” relationships.
4. Product Entity: Positioned at the top-right, connected to the OrderItem entity and to the Category entity with a “Many-to-One” relationship.
5. Category Entity: Positioned above the Product entity, connected to the Product entity with a “One-to-Many” relationship.

---

Example Relationships

• User – Order: A user can place multiple orders.
• Order – OrderItem: Each order can contain multiple order items.
• OrderItem – Product: Each order item refers to a specific product.
• Product – Category: Each product belongs to one category.