Understanding Sparx EA Database Schema: A Practical Guide for Architects and Integrators

Introduction

Sparx Systems Enterprise Architect (EA) provides more than diagramming—it is backed by a rich, relational database schema that enables deep integration, automation, and reporting across enterprise architecture projects. Whether you are a solution architect, data analyst, or EA tool integrator, understanding this schema is essential for advanced use cases such as querying model content, automating integrity checks, and exporting metadata for governance or analytics.

1. The Core Structure of Sparx EA's Database Schema

The EA schema consists of dozens of interrelated tables. These can be grouped into several logical layers:

• Core modeling: t_object, t_package, t_diagram, t_connector
• Attributes and behaviors: t_attribute, t_operation, t_effort
• Metadata and tags: t_objectproperties, t_taggedvalue
• Visual layout: t_diagramobjects, t_diagramlinks
• Audit and security: t_secuser, t_secgroup, t_objectlocks

2. Key Tables and Their Attributes

t_object

This is the core table for all modeling elements:

• Object_ID: Primary key, used throughout the schema
• Name: The element's label
• Object_Type: E.g., 'Class', 'ApplicationComponent', 'BusinessProcess'
• Package_ID: Foreign key to t_package
• Stereotype: Optional, e.g., 'archimate3::BusinessService'
• CreatedDate, ModifiedDate: For change tracking

t_package

• Package_ID: Unique ID of the package
• Name: Folder or view name
• Parent_ID: Supports hierarchical nesting
• Flags: Stores project-specific configuration or baseline metadata

t_connector

• Connector_ID: Primary key for the relationship
• Connector_Type: E.g., 'Association', 'Realization', 'Aggregation'
• Start_Object_ID, End_Object_ID: Source and target
• Direction: 'Source -> Target' or bidirectional
• Subtype: Extension metadata, often used in UML/Archimate

t_attribute and t_operation

Contain structural and behavioral metadata for elements like classes:

• t_attribute: Attributes, their types, visibility, and tagged values
• t_operation: Methods and their parameters

t_objectproperties and t_taggedvalue

Used to store custom metadata and user-defined properties:

• t_objectproperties: System-wide tagged values
• t_taggedvalue: Connector-specific tagged values

t_diagram and t_diagramobjects

For visual layout and diagram configuration:

• t_diagram: Metadata like diagram type, orientation, locked status
• t_diagramobjects: Coordinates and styling of each object on the canvas

3. Real-World Use Cases of Schema Queries

• Impact Analysis: Identify which systems would be affected if a process changes
• Custom Dashboards: Feed KPI-compliant dashboards in Power BI or Tableau
• Portfolio Reviews: Report on outdated, redundant, or under-documented elements
• Integration Mapping: Generate service dependency maps for APIs or data exchanges

4. Query Example: Orphaned Elements

SELECT o.Name, o.Object_Type
FROM t_object o
LEFT JOIN t_diagramobjects do ON o.Object_ID = do.Object_ID
WHERE do.Object_ID IS NULL;

This helps identify elements that exist in the model but are not visualized in any diagram—a common source of clutter.

5. Schema Governance and Best Practices

• Use read-only access for analysis: Protect integrity and prevent corruption
• Document schema changes: Track table/column usage especially across EA versions
• Avoid writing directly to the DB: Use the EA API for inserts or updates
• Standardize stereotypes and naming conventions: Easier querying and transformation

6. Tools for Exploring the Schema

• DB Management: PostgreSQL PgAdmin, SQL Server Management Studio, DBeaver
• Visualization: Power BI, Metabase, Grafana (with SQL backends)
• Validation: jArchi for scripting or EA scripting console

7. Combining Schema Access with the EA API

The EA API can be used in tandem with direct SQL access. For example:

• Use SQL to identify problem elements (e.g., incorrect relationships)
• Use API to generate fix scripts or updates programmatically
• Cross-check model consistency before export or integration

SQL Cheat Sheet: Querying the EA Schema

1. List All Application Components
SELECT Name, Object_Type
FROM t_object
WHERE Object_Type = 'ApplicationComponent';

2. Diagrams Modified in the Last 30 Days
SELECT Name, ModifiedDate
FROM t_diagram
WHERE ModifiedDate > DATEADD(day, -30, GETDATE());

3. Orphaned Elements (Not in Diagrams)
SELECT o.Name, o.Object_Type
FROM t_object o
LEFT JOIN t_diagramobjects do ON o.Object_ID = do.Object_ID
WHERE do.Object_ID IS NULL;

4. Extract Tagged Values
SELECT o.Name, tv.Property, tv.Value
FROM t_object o
JOIN t_objectproperties tv ON o.Object_ID = tv.Object_ID;

5. No Incoming Connectors
SELECT o.Name
FROM t_object o
LEFT JOIN t_connector c ON o.Object_ID = c.End_Object_ID
WHERE c.Connector_ID IS NULL;

6. Relationship Count Per Element
SELECT o.Name, COUNT(c.Connector_ID) AS ConnectionCount
FROM t_object o
LEFT JOIN t_connector c ON o.Object_ID IN (c.Start_Object_ID, c.End_Object_ID)
GROUP BY o.Name;

7. Recently Added Classes
SELECT Name, CreatedDate
FROM t_object
WHERE Object_Type = 'Class'
ORDER BY CreatedDate DESC;

8. Views and Their Package
SELECT d.Name AS DiagramName, p.Name AS PackageName
FROM t_diagram d
JOIN t_package p ON d.Package_ID = p.Package_ID;

Conclusion

The Sparx EA database schema is an invaluable resource for architects and integrators. From customizing metadata exports to validating modeling standards, mastering the schema enables deeper insights, better automation, and tighter alignment with enterprise goals. When combined with the EA API and tools like jArchi or BI dashboards, it unlocks powerful possibilities for maintaining high-quality architecture repositories.

Sparx EA Database Schema, t_object, EA Metadata, EA Repository, Enterprise Architect SQL Queries, t_diagramobjects, EA Integration, EA Tagged Values, EA Automation, EA Architecture Repository, EA Schema Guide

If you’d like hands-on training tailored to your team (Sparx Enterprise Architect, ArchiMate, TOGAF, BPMN, SysML, or the Archi tool), you can reach us via our contact page.