The Hidden World of Data Management
Ahmed Rezika, SimpleWays OU
Posted 3/6/2025
Data Management – A Hidden World
In my previous article, What Happens When I Click This Button? we explored how a single click in SCADA, ERP, or CMMS systems sets off a chain reaction—triggering applications, APIs, processing, and storage. But what about the data behind that click? Where does it go, and why does it matter?
For many maintenance professionals, data is an afterthought. You enter details into a work order or failure report, maybe glance at a KPI dashboard, and move on. In the old days, that data would gather dust in a filing cabinet. Today, it fuels reports, AI-driven insights, and predictive analytics that shape maintenance decisions.
The key takeaway? Your CMMS, ERP, or AI tools are only as good as the data they rely on. The same applies when crafting a manual report from accumulated data—whether digital or on paper. Understanding how data is structured, connected, and processed can unlock smarter maintenance strategies—no data science degree required [1].
In this article, we’ll uncover the hidden architecture of data in maintenance: how it’s stored, linked, and transformed into valuable insights. Whether you’re hands-on with wrenches or fine-tuning digital systems, a deeper look at data management will help you get the most out of your tools. Let’s dive in.
The last piece of the puzzle before the hands-on: Relational vs. Non-Relational Databases
Relational Databases (SQL)
Structure: Data is stored in tables with rows and columns, linked by keys (primary/foreign keys).
DBMS Examples: MySQL, PostgreSQL, Microsoft SQL Server.
Use Case in Maintenance: Ideal for structured data like work orders, asset histories, and inspection reports. Relational databases excel at handling complex queries and maintaining data integrity through relationships.
Example: A CMMS system storing work orders (table 1) linked to assets (table 2) via Asset ID.
Non-Relational Databases (NoSQL) [3]
Structure: Data is stored in flexible formats like key-value pairs, documents, or graphs.
DBMS Examples: MongoDB, Cassandra, Redis.
Use Case in Maintenance: Best for unstructured or semi-structured data, such as sensor data from IoT devices, logs, or multimedia files. NoSQL databases scale horizontally and handle high-velocity data well.
Example: Storing real-time sensor data from SCADA systems, where each sensor generates a stream of timestamped readings.
Why Choose One Over the Other?
Prefer Relational Databases when: Data is structured and relationships are critical (e.g., linking work orders to assets) or you need complex queries and strong data integrity (e.g., generating detailed maintenance reports).
Prefer Non-Relational Databases when: Data is unstructured or high-volume (e.g., sensor data, logs) or, scalability and speed for real-time data ingestion are priorities.
In maintenance, relational databases are often preferred for core operations (e.g., CMMS, ERP) due to their structured nature and ability to handle complex relationships. However, non-relational databases are gaining traction for IoT and predictive maintenance applications, where handling large volumes of real-time data is key.
I personally explored storing complete files—whether PDFs, Excel sheets, or Word documents—inside a database and retrieving them through another user. It was astonishing to see that a full file could be stored and restored from a database. Later, I learned that entire WordPress websites, including all posts and plugins, are stored in databases, further proving their versatility beyond just structured tables and records.
Practice Data Management Online
Practicing SQL online with real data, databases, and queries is a fantastic way to build and refine digital skills. W3Schools (W3S) offers a comprehensive platform for learning, making it an excellent resource for beginners and those looking to refresh their knowledge. One of its most valuable features is the interactive SQL sandbox, where you can modify example queries or write your own using the provided dataset.
At this URL: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all , you’ll find a preloaded database with tables that simulate a real-world store application. On the right-hand side, you’ll see a list of tables, including Customers, Orders, and more, each populated with sample data.
If you explore these tables, you’ll notice that each one has a primary key—for example, CustomerID in the Customers table and OrderID in the Orders table. The Orders table also contains a CustomerID column, but as a foreign key, which links each order to a specific customer. This design ensures efficient data organization, prevents redundant modifications to the Customers table, and maintains controlled access so that different entities interacting with the system can only view the data relevant to them.
Now, if you want to retrieve data from multiple tables and apply primary and foreign key principles, copy and paste the following SQL query into the SQL Statement Window, then click Run SQL:
SELECT
Customers.CustomerID,
Customers.CustomerName,
Orders.OrderID,
Orders.OrderDate,
OrderDetails.ProductID,
OrderDetails.Quantity,
Products.ProductID,
Products.ProductName
FROM Customers, Orders, OrderDetails, Products
WHERE Customers.CustomerID = Orders.CustomerID
AND Orders.OrderID = OrderDetails.OrderID
AND OrderDetails.ProductID = Products.ProductID
AND Customers.CustomerID = 81
ORDER BY Orders.OrderID;
To better understand how this query works, you can paste it into any AI-powered assistant for a detailed explanation. In summary:
- The SELECT clause specifies which columns (attributes) to retrieve.
- The FROM and WHERE clauses define the tables and conditions for filtering records.
- The query filters results using CustomerID = 81 as an example.
- Finally, the ORDER BY clause sorts the output by OrderID.
This hands-on approach is a great way to explore SQL in action and strengthen your understanding of relational databases. Happy querying!
Data Design: Examples from Maintenance
Data design is the process of structuring and organizing information within a system to ensure efficient storage, retrieval, and management of data. It involves defining data types, relationships between data entities, grouping, and formats to optimize system performance and usability.
Maintenance Data Design with Schema Examples
In maintenance management, proper data design and modeling are essential for organizing information related to assets, work orders, technicians, and inventory. Below are key examples of how these concepts are applied:
1. Assets Table
Purpose: Stores details of all assets requiring maintenance.
Schema Example:
2. Work Orders Table
Purpose: Manages maintenance requests for assets.
Schema Example:
3. Technicians Table
Purpose: Stores information about maintenance personnel.
Schema Example:
4. Technician Work Orders Table (Many-to-Many Relationship)
Purpose: Links technicians to assigned work orders, allowing multiple technicians to handle a single task.
Schema Example:
Why This Structure Matters
Relational Integrity: Foreign keys ensure relationships between tables are maintained.
Efficiency: The separation of data into different tables avoids redundancy and improves query performance.
Scalability: The model supports business growth by accommodating more assets, work orders, and technicians without restructuring.
Conclusion: From Paper Files to the Digital Realm – Keeping Data Well-Organized for Smarter Maintenance
In today’s discussion, we explored the critical role of data management in modern maintenance systems, emphasizing how your daily interactions with CMMS, ERP, or SCADA systems lay the foundation for smarter decision-making and AI-driven insights. We compared relational databases (structured, table-based systems like MySQL) with non-relational databases (flexible, scalable systems like MongoDB), highlighting their use cases in maintenance. Relational databases excel at managing structured data like work orders and asset histories, while non-relational databases are ideal for handling unstructured, high-velocity data like IoT sensor readings.
We also delved into the architecture of data systems, explaining how metadata, schema, primary/foreign keys, and indexes ensure data is organized, retrievable, and actionable. Understanding these concepts helps you see how your data entries—whether logging a work order or recording an inspection—feed into a larger system that powers reports, KPIs, and even AI tools like predictive maintenance algorithms.
The key takeaway? Your data is the lifeblood of modern maintenance[4]. By ensuring accuracy, consistency, and completeness in your data entries, you’re not just completing a task—you’re enabling faster troubleshooting, smarter decisions, and a future where AI can help predict failures and optimize operations.
Must-Know Jargon
Metadata: Information that describes data (e.g., Work Order ID, Asset Condition).
Schema: The blueprint of a database, defining tables, fields, and data types.
Primary/Foreign Keys: Unique identifiers that link related data across tables.
Indexes: Tools that speed up data retrieval by acting like a book index.
Relational Database: A structured system using tables and relationships (e.g., MySQL).
Non-Relational Database: A flexible system for unstructured or high-volume data (e.g., MongoDB).
Query: A request to retrieve or analyze data from a database.
Normalization: Organizing data to reduce redundancy and improve efficiency.
By mastering these concepts, you’ll not only improve your data management skills but also bridge the gap between hands-on maintenance and the data-driven tools shaping the future of the industry.
Stay tuned as we uncover the building blocks of the digital world and equip you with the knowledge to navigate it confidently.
References – The Hidden World of Data Management
1. Garcia-Molina, & Hector, Hector & Ullman, & D, Jeffrey & Widom, & Jennifer, (2002). Database Systems: The Complete Book. https://www.researchgate.net/publication/200034291_Database_Systems_The_Complete_Book
2. Introduction to Databases,  Lecture Notes by University of California at Berkeley, Learn SQL, https://cs186berkeley.net/notes/note13/
3. Microsoft-Azure-Learn, Non-relational data and NoSQL,  https://learn.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data
4. Ahmed Rezika. Aug 2020, Can the Maintenance live without its Blood?, https://simpleways.life/maintenance-system-approach-1/
Related Articles
Too Small for a CMMS? Think Again
The Role of Information Technology in Plant Reliability
The Future of CMMS
