Create a professional SQL-style ER Diagram for an IT Asset Management System.
IMPORTANT FORMAT REQUIREMENTS:
* Every entity/table must look like a horizontal SQL table preview
* Column names should appear in the top row
* Add (PK) beside primary keys
* Add (FK) beside foreign keys
* Do NOT show data types
* Under each table header, show 2–3 sample rows exactly like SQL table output
* Use clean aligned rows and columns
* Use enterprise database styling
* Use crow’s foot notation for relationships
* Use visible FK relationship arrows between tables
* Arrange tables neatly with minimal line crossing
* Keep master tables grouped together
* Keep transactional tables grouped together
* Keep detail tables grouped together
* Keep relationship/history tables grouped together
============================================================
MASTER TABLES
=============
departments
| department_id (PK) | department_name |
| ------------------ | --------------- |
| 1 | IT |
| 2 | HR |
| 3 | Finance |
---
locations
| location_id (PK) | location_name |
| ---------------- | ------------- |
| 1 | Chennai |
| 2 | Mumbai |
| 3 | Delhi |
---
designations
| designation_id (PK) | designation_name |
| ------------------- | ---------------- |
| 1 | System Engineer |
| 2 | HR Manager |
| 3 | Network Admin |
---
employee_types
| employee_type_id (PK) | employee_type_name |
| --------------------- | ------------------ |
| 1 | AAI |
| 2 | Contract |
| 3 | Intern |
---
brands
| brand_id (PK) | brand_name |
| ------------- | ---------- |
| 1 | Dell |
| 2 | HP |
| 3 | Lenovo |
---
device_models
| model_id (PK) | brand_id (FK) | model_name |
| ------------- | ------------- | ------------- |
| 1 | 1 | OptiPlex 7090 |
| 2 | 2 | EliteDesk 800 |
| 3 | 3 | ThinkPad E14 |
Relationship:
brands.brand_id → device_models.brand_id
---
vendors
| vendor_id (PK) | vendor_name | contact_person |
| -------------- | ------------ | -------------- |
| 1 | Dell India | Rajesh Kumar |
| 2 | HP India | Anita Sharma |
| 3 | Lenovo India | Vikas Singh |
---
asset_types
| asset_type_id (PK) | asset_type_name |
| ------------------ | --------------- |
| 1 | CPU |
| 2 | Laptop |
| 3 | Monitor |
---
asset_status
| status_id (PK) | status_name |
| -------------- | ----------- |
| 1 | Active |
| 2 | Repair |
| 3 | Assigned |
---
operating_systems
| os_id (PK) | os_name | os_version |
| ---------- | ------- | ---------- |
| 1 | Windows | 11 Pro |
| 2 | Ubuntu | 22.04 |
| 3 | macOS | Sonoma |
---
processors
| processor_id (PK) | processor_name |
| ----------------- | -------------- |
| 1 | Intel i5 |
| 2 | Intel i7 |
| 3 | Ryzen 7 |
---
ram_types
| ram_type_id (PK) | ram_type_name |
| ---------------- | ------------- |
| 1 | DDR3 |
| 2 | DDR4 |
| 3 | DDR5 |
---
storage_types
| storage_type_id (PK) | storage_type_name |
| -------------------- | ----------------- |
| 1 | HDD |
| 2 | SSD |
| 3 | NVMe SSD |
---
software_master
| software_id (PK) | software_name | software_version |
| ---------------- | ------------- | ---------------- |
| 1 | MS Office | 2021 |
| 2 | Photoshop | 2024 |
| 3 | VS Code | 1.90 |
============================================================
TRANSACTION TABLES
==================
users
| user_id (PK) | employee_name | designation_id (FK) | department_id (FK) | employee_type_id (FK) | location_id (FK) |
| ------------ | ------------- | ------------------- | ------------------ | --------------------- | ---------------- |
| EMP001 | Agneay B Nair | 1 | 1 | 1 | 1 |
| EMP002 | Rahul Sharma | 2 | 2 | 1 | 2 |
| EMP003 | Priya Menon | 3 | 3 | 2 | 1 |
Relationships:
* designations.designation_id → users.designation_id
* departments.department_id → users.department_id
* employee_types.employee_type_id → users.employee_type_id
* locations.location_id → users.location_id
---
assets
| asset_id (PK) | asset_type_id (FK) | model_id (FK) | vendor_id (FK) | serial_number |
| ------------- | ------------------ | ------------- | -------------- | ------------- |
| CPU001 | 1 | 1 | 1 | SNCPU101 |
| CPU002 | 1 | 2 | 2 | SNCPU102 |
| LAP001 | 2 | 3 | 3 | SNLAP201 |
Relationships:
* asset_types.asset_type_id → assets.asset_type_id
* device_models.model_id → assets.model_id
* vendors.vendor_id → assets.vendor_id
============================================================
DETAIL TABLES
=============
cpu_details
| asset_id (PK)(FK) | processor_id (FK) | ram_size | ram_type_id (FK) | storage_size | storage_type_id (FK) | os_id (FK) |
| ----------------- | ----------------- | -------- | ---------------- | ------------ | -------------------- | ---------- |
| CPU001 | 2 | 16GB | 2 | 1TB | 1 | 1 |
| CPU002 | 1 | 8GB | 2 | 512GB | 2 | 2 |
Relationships:
* assets.asset_id → cpu_details.asset_id
* processors.processor_id → cpu_details.processor_id
* ram_types.ram_type_id → cpu_details.ram_type_id
* storage_types.storage_type_id → cpu_details.storage_type_id
* operating_systems.os_id → cpu_details.os_id
---
laptop_details
| asset_id (PK)(FK) | processor_id (FK) | ram_size | ram_type_id (FK) | storage_size | storage_type_id (FK) | os_id (FK) |
| ----------------- | ----------------- | -------- | ---------------- | ------------ | -------------------- | ---------- |
| LAP001 | 3 | 16GB | 3 | 512GB | 3 | 1 |
Relationships:
* assets.asset_id → laptop_details.asset_id
* processors.processor_id → laptop_details.processor_id
* ram_types.ram_type_id → laptop_details.ram_type_id
* storage_types.storage_type_id → laptop_details.storage_type_id
* operating_systems.os_id → laptop_details.os_id
============================================================
RELATIONSHIP / HISTORY TABLES
=============================
user_asset_assignment
| assignment_id (PK) | user_id (FK) | asset_id (FK) | assigned_date |
| ------------------ | ------------ | ------------- | ------------- |
| 1 | EMP001 | CPU001 | 2024-01-15 |
| 2 | EMP002 | LAP001 | 2024-03-20 |
| 3 | EMP003 | CPU002 | 2024-04-10 |
Relationships:
* users.user_id → user_asset_assignment.user_id
* assets.asset_id → user_asset_assignment.asset_id
---
asset_assignment_history
| history_id (PK) | asset_id (FK) | user_id (FK) | assignment_status |
| --------------- | ------------- | ------------ | ----------------- |
| 1 | CPU001 | EMP001 | Assigned |
| 2 | CPU002 | EMP003 | Assigned |
| 3 | LAP001 | EMP002 | Assigned |
Relationships:
* users.user_id → asset_assignment_history.user_id
* assets.asset_id → asset_assignment_history.asset_id
---
asset_software_installation
| installation_id (PK) | asset_id (FK) | software_id (FK) | install_date |
| -------------------- | ------------- | ---------------- | ------------ |
| 1 | CPU001 | 1 | 2024-01-11 |
| 2 | CPU001 | 2 | 2024-01-12 |
| 3 | LAP001 | 3 | 2024-03-16 |
Relationships:
* assets.asset_id → asset_software_installation.asset_id
* software_master.software_id → asset_software_installation.software_id
FINAL REQUIREMENTS:
* Make the diagram visually similar to SQL table previews
* Use professional spacing and alignment
* Use dark/light modern database architecture style
* Show all FK relationship arrows clearly
* Use crow’s foot notation
* The final diagram should look like a real enterprise relational database schema
Your privacy choices
