Create Database ERD Inventory System
To design an Entity-Relationship Diagram (ERD) for an Inventory Management System, we need to identify the key entities and their relationships. Below is a common example of entities and relationships for an inventory system.
### Entities and Attributes:
1. **Product**
- `ProductID` (Primary Key)
- `ProductName`
- `Description`
- `Price`
- `QuantityInStock`
- `ReorderLevel`
- `CategoryID` (Foreign Key)
- `SupplierID` (Foreign Key)
2. **Category**
- `CategoryID` (Primary Key)
- `CategoryName`
- `Description`
3. **Supplier**
- `SupplierID` (Primary Key)
- `SupplierName`
- `ContactName`
- `Phone`
- `Email`
- `Address`
4. **PurchaseOrder**
- `PurchaseOrderID` (Primary Key)
- `OrderDate`
- `SupplierID` (Foreign Key)
- `TotalAmount`
5. **PurchaseOrderDetail**
- `PurchaseOrderDetailID` (Primary Key)
- `PurchaseOrderID` (Foreign Key)
- `ProductID` (Foreign Key)
- `Quantity`
- `UnitPrice`
6. **SalesOrder**
- `SalesOrderID` (Primary Key)
- `OrderDate`
- `CustomerID` (Foreign Key)
- `TotalAmount`
7. **SalesOrderDetail**
- `SalesOrderDetailID` (Primary Key)
- `SalesOrderID` (Foreign Key)
- `ProductID` (Foreign Key)
- `Quantity`
- `UnitPrice`
8. **Customer**
- `CustomerID` (Primary Key)
- `CustomerName`
- `Phone`
- `Email`
- `Address`
### Relationships:
- A **Category** can have many **Products**, but each product belongs to one **Category**.
- A **Supplier** can supply many **Products**, but each product comes from one **Supplier**.
- A **PurchaseOrder** can have many **PurchaseOrderDetails**, but each detail belongs to one **PurchaseOrder**.
- A **Product** can appear in many **PurchaseOrderDetails** and many **SalesOrderDetails**.
- A **Customer** can have many **SalesOrders**, but each **SalesOrder** belongs to one **Customer**.
### ERD Diagram:
- **Product** ⇔ **Category** (Many-to-One)
- **Product** ⇔ **Supplier** (Many-to-One)
- **PurchaseOrder** ⇔ **Supplier** (Many-to-One)
- **PurchaseOrder** ⇔ **PurchaseOrderDetail** (One-to-Many)
- **PurchaseOrderDetail** ⇔ **Product** (Many-to-One)
- **SalesOrder** ⇔ **Customer** (Many-to-One)
- **SalesOrder** ⇔ **SalesOrderDetail** (One-to-Many)
- **SalesOrderDetail** ⇔ **Product** (Many-to-One)
This description can now be represented in a visual ERD diagram using tools like LucidChart, dbdiagram.io, or MySQL Workbench. Let me know if you need further assistance in creating the visual diagram.