Skip to content

README.md

Latest commit

 

History

History
274 lines (186 loc) · 15.5 KB

README.md

File metadata and controls

274 lines (186 loc) · 15.5 KB

🏭 Asset Management Data Model — Holistic Overview

⬅️ Go Back to Production Monitoring System

Purpose

This document provides a comprehensive overview of the Asset Management System (AMS) relational data model. It connects all modular components — from organizational hierarchy to asset communication, alerts, maintenance, and specifications — into one cohesive structure.

The AMS is designed for industrial environments, enabling tracking, monitoring, and management of assets throughout their lifecycle, across organizational and physical hierarchies.

System Overview

The AMS data model integrates assets into a multi-layered hierarchy that connects business context, operational context, and technical metadata.

Organization → Unit → Line → Department → Location → Asset
                                           ↓
                                   [AssetGroup / Type / Make / Manufacture]
                                           ↓
                                  Communication & Monitoring
                                           ↓
                                Alerts | Events | Maintenance | Activities
                                           ↓
                             Documents | Specifications | Components

ERD Visualization:

Asset Management ERD

Data Layer Overview

Layer Module Key Entities Purpose
Layer 1 Hierarchy Organization, Unit, Line, Department, Location Defines where assets physically and logically belong
Layer 2 Operations Event, EventType, Maintenance, AssetTools, Activities, Tasks Defines how assets perform and are maintained
Layer 3 Communication AssetCommunication, AssetCommunicationType, AssetCommunicationHistory Defines how assets connect and communicate
Layer 4 Monitoring Alerts, AlertType Defines how issues and notifications are managed
Layer 5 Specifications Documents, Specifications, ComponentMappings Defines how metadata and technical references are stored

Layer 1. Hierarchical Module

Asset Management ERD

Mini ERD Visualization:

Organization
 └─< Unit
      └─< Line
           └─< Department
                └─< Location
                     └─< Asset

Overview

This section models the organizational hierarchy and asset placement in physical or logical locations. Every asset belongs to a location, which is part of a department, under a line, which belongs to a unit, and finally, part of an organization.

Tables & Relationships

Table Primary Key Foreign Key Description
Organization OrgID - Represents top-level organization
Unit UnitID OrgID → Organization A unit belongs to an organization
Line LineID UnitID → Unit Lines under a unit
Department DepartmentID LineID → Line Departments under a line
Location LocationID DepartmentID → Department Physical locations of assets; includes Lat & Lon
AssetType TypeID - Categorizes asset (e.g., Pump, Motor)
Asset AssetID LocationID → Location, AssetTypeID → AssetType, ParentAssetID → Asset (optional) Each asset is tied to a location, has a type, and optionally a parent asset if it’s a component

Layer 2. Operations Module

Asset Management ERD

Mini ERD Visualization:

Asset
  ├─< Event >─ EventType
  ├─< Maintenance >─ EventType
  │      └─< MaintenanceTools >─ AssetTools
  └─< Activities >─ Tasks

Overview

This module manages all asset operations, including:

  • Events generated by assets
  • Maintenance schedules and execution
  • Tasks and activities performed on assets
  • Tools used during maintenance

It ensures operational traceability, scheduling, and tracking of asset performance.

Tables & Relationships

Table Primary Key Foreign Key Description
Asset AssetID ParentAssetID → Asset Core asset table, optional parent for sub-assets
EventType EventTypeID - Defines types of events (e.g., Failure, Warning)
Event EventID AssetID → Asset, EventTypeID → EventType Records occurrences or incidents related to an asset
Tasks TaskID - List of predefined tasks or activities
Activities AssetID + TaskID AssetID → Asset, TaskID → Tasks Tracks activities performed on assets on a given date
AssetTools ToolID - List of tools available for maintenance or operational tasks
Maintenance MaintenanceID AssetID → Asset, EventTypeID → EventType Tracks scheduled or completed maintenance for an asset
MaintenanceTools MaintenanceID + ToolID MaintenanceID → Maintenance, ToolID → AssetTools Maps tools used for each maintenance event

Perfect! Let’s group these tables into a “Asset Communication Module”. I’ll provide both the DbDiagram.io syntax and the documentation with explanation.

Layer 3. Device / Communication Module**

Asset Management ERD

Mini ERD Visualization:

Asset
  └─< AssetCommunication >─ AssetCommunicationType
         └─< AssetCommunicationHistory

Overview

This module manages all communication interfaces of assets, including:

  • Communication types and protocols (AssetCommunicationType)
  • Actual communication endpoints (AssetCommunication)
  • History of connections and disconnections (AssetCommunicationHistory)

It enables monitoring of connectivity, troubleshooting, and operational tracking of networked assets.

Tables & Relationships

Table Primary Key Foreign Key Description
Asset AssetID ParentAssetID → Asset Core asset table. Communication is linked to assets.
AssetCommunicationType CommunicationTypeID - Defines type of communication (e.g., Ethernet, Modbus, OPC-UA) and allowed protocol/ports.
AssetCommunication AssetCommunicationID AssetID → Asset, CommunicationTypeID → AssetCommunicationType Represents IP/port configurations of asset communication interfaces.
AssetCommunicationHistory HistoryID AssetCommunicationID → AssetCommunication Tracks each communication session, start/end times, and disconnect reasons.

Layer 4. Monitoring Module

Asset Management ERD

Mini ERD Visualization:

AssetType     AssetMake     AssetManufacture     AssetGroup
      \           |                 |                  /
                       Asset
                        |
                Parent-Child Assets
                  AssetComponents
                        |
                    Alerts
                        |
                    AlertType

Overview

This module manages the metadata of assets (type, make, manufacturer, group), component relationships, and alerts generated by assets. It ensures structured categorization and operational monitoring.

Tables & Relationships

Table Primary Key Foreign Key Description
Asset AssetID AssetTypeID → AssetType, AssetMakeID → AssetMake, AssetManufactureID → AssetManufacture, AssetGroupID → AssetGroup, ParentAssetID → Asset Core asset table; optionally linked to sub-assets via ParentAssetID.
AssetType TypeID - Categorizes the asset (e.g., Motor, Pump, Sensor).
AssetMake MakeID - Brand or make of the asset.
AssetManufacture ManufactureID - Manufacturer of the asset.
AssetGroup AssetGroupID - Logical grouping of assets (e.g., Production Line Equipment).
AssetComponents AssetID + ComponentAssetID AssetID → Asset, ComponentAssetID → Asset Maps parent assets to their sub-assets/components.
AlertType AlertTypeID - Defines types of alerts (e.g., Critical, Warning).
Alerts AlertID AssetID → Asset, AlertTypeID → AlertType Logs alerts generated by assets, linked to alert types.

Perfect! Let’s group these into a “Asset Metadata, Components & Specifications Module”, which focuses on documents, specifications, and components for assets. I’ll provide DbDiagram.io syntax and documentation with explanation.

layer 5. Specfication Module

Asset Management ERD

Mini ERD Visualization:

Asset
  ├─< AssetComponentsMapping >─ Asset (sub-assets)
  ├─< AssetDocumentMapping >─ AssetDocument ─ AssetDocumentType
  └─< AssetSpecificationMapping >─ Specification

Overview

This module manages the metadata and auxiliary information of assets, including:

  • Component/sub-asset relationships
  • Asset documents and their types
  • Specifications linked to assets

It provides a structured way to manage asset documentation, compliance, and technical specifications.

Tables & Relationships

Table Primary Key Foreign Key Description
Asset AssetID ParentAssetID → Asset Core asset table; optionally linked to sub-assets via ParentAssetID.
AssetComponentsMapping AssetID + ComponentAssetID AssetID → Asset, ComponentAssetID → Asset Maps parent assets to their sub-assets/components.
AssetDocumentType DocumentTypeID - Defines document types (e.g., Manual, Certificate).
AssetDocument DocumentID DocumentTypeID → AssetDocumentType Stores documents related to assets.
AssetDocumentMapping AssetID + DocumentID AssetID → Asset, DocumentID → AssetDocument Links documents to assets (M:N relationship).
Specification SpecificationID - Defines specifications or technical parameters of assets.
AssetSpecificationMapping AssetID + SpecificationID AssetID → Asset, SpecificationID → Specification Maps specifications to assets (M:N relationship).

Summary

This documentation defines a complete, relational, and modular structure for managing enterprise assets. It enables:

  • End-to-end traceability
  • Integration across operational systems (MES, CMMS, IIoT, etc.)
  • Simplified data analytics and reporting

Links

  1. ⬅️ Go Back to Production Monitoring System