Entity-Relationship Diagram (ERD)

An ERD is a visual representation of the data model that represents the structure of a database. It is a powerful tool used in database design to illustrate the logical structure of a database in a graphical way. ERDs are particularly useful in understanding the relationships between different entities in a system.

ERD diagram concepts

Entities: Entities are objects or concepts that are represented in the database and are typically nouns. For example, in a university database, entities might include Student, Course, and Professor.

Attributes: Attributes are properties or characteristics that describe the entities. For example, a Student entity may have attributes like StudentID, Name, and DateOfBirth.

Relationships: Relationships depict how entities are related to each other. They establish connections between entities and can be one-to-one, one-to-many, or many-to-many. For instance, a Student entity might be related to a Course entity in a "takes" relationship.

Cardinality: Cardinality defines the numerical relationship between entities in a relationship. It indicates how many instances of one entity are related to another. Common cardinalities include one-to-one (1:1), one-to-many (1:N), and many-to-many (M:N).

Primary Key: A primary key is a unique identifier for a record within an entity. It helps ensure each record can be uniquely identified.

Foreign Key: A foreign key is a field in one table that refers to the primary key in another table. It establishes a link between the two tables.

Weak Entity: A weak entity is an entity that cannot be uniquely identified by its own attributes alone and relies on a related entity to provide the identification.

Identify Main Entities:

User Entities:
-Customer
-Administrator
-Seller

Product Entities:
-Product
-Category
-Brand

Order Entities:
-Order
-Order Item

Payment Entities:
-Payment
-Payment Method

Identify Attributes:

Customer Entity Attributes:
-CustomerID (Primary Key)
-Name
-Email
-Address
-Phone

Product Entity Attributes:

-ProductID (Primary Key)
-Name
-Price
-Description
-StockQuantity

Order Entity Attributes:

-OrderID (Primary Key)
-OrderDate
-TotalAmount
-Status (e.g., pending, shipped)

Payment Entity Attributes:

-PaymentID (Primary Key)
-Amount
-PaymentDate
-Status (e.g., successful, pending)

Define Relationships:

Customer-Order Relationship:
-One customer can place multiple orders (One-to-Many).
-Add a foreign key in the Order entity referring to CustomerID.

Order-Order Item Relationship:
-One order can have multiple order items, and each order item belongs to only one order (One-to-Many).
-Add a foreign key in the Order Item entity referring to OrderID.

Product-Order Item Relationship:
-One product can be included in multiple order items, and each order item is associated with one product (Many-to-One).
-Add a foreign key in the Order Item entity referring to ProductID.

Customer-Payment Relationship:
-One customer can make multiple payments (One-to-Many).
-Add a foreign key in the Payment entity referring to CustomerID.

Cardinality:

Customer-Order Cardinality:
-One customer can place many orders, but each order is placed by one customer.
Order-Order Item Cardinality:
-One order can have many order items, but each order item belongs to one order.
Product-Order Item Cardinality:
-One product can be in many order items, but each order item is associated with one product.
Customer-Payment Cardinality:
-One customer can make many payments, but each payment is made by one customer.

Final ERD

Entity–relationship model