2.4 System Members, Attributes & Indexing

2.4 System Members, Attributes & Indexing

Designing robust enterprise domain models requires a granular understanding of how attribute data types, built-in system tracking members, and physical database indexes operate under the hood. Intermediate Mendix developers must be able to select the correct precision for monetary values, configure timezone localization accurately, leverage system members for data security, and design high-performance database indexes that eliminate full table scans in multi-million-row production tables.

---

System Members: Automated Auditing & Row-Level Security Hooks

In Studio Pro, opening an entity's properties dialog reveals a section titled System members. Enabling these checkboxes instructs the Mendix Runtime to automatically track metadata for every row without requiring custom microflow logic:

The Four System Members

1. createdDate (DateTime): Automatically stamped with the exact UTC timestamp when the record is first committed to the database. It is immutable and cannot be modified by application logic.
2. changedDate (DateTime): Automatically updated to the current UTC timestamp every time an existing record is committed with altered attribute or association values.
3. owner (Association to System.User): Automatically set to reference the System.User instance representing the logged-in user who created the record.
4. changedBy (Association to System.User): Automatically updated to reference the System.User instance representing the user who executed the most recent commit on the record.

Leveraging System Members in Entity Access Rules

Beyond passive auditing, system members are central to Row-Level Multi-Tenant Security. In high-security applications, end users should only be permitted to view or edit records they personally authored. Because owner is populated natively by the runtime, developers can define an XPath access rule on the entity:

[System.owner = '[%CurrentUser%]']

Because this rule is evaluated directly by the database query engine, users can never query, inspect, or tamper with records authored by other users, completely securing the application at the data layer.

Performance Consideration: Enabling system members on high-throughput staging tables or temporary integration entities adds slight storage and indexing overhead. Enable them deliberately where auditability or row-level security is required.

---

Attribute Data Types: Deep Technical Characteristics

Selecting the wrong data type during initial domain modeling can cause rounding inaccuracies, time zone shifts, or database lock contention down the road:

1. AutoNumber: Database Sequences & The "Gap" Reality

• Mechanism: Managed directly by the underlying database engine using native sequences (e.g., CREATE SEQUENCE in PostgreSQL). Values increment sequentially (1, 2, 3...).
• Immutability: An AutoNumber cannot be edited, overwritten, or updated by microflow expressions or user input.
• The Rollback Behavior: Database sequences increment outside the transactional rollback boundary to maintain high concurrency. If a microflow creates an entity with AutoNumber 5012, but an error occurs later in the microflow triggering a rollback, number 5012 is permanently consumed. The next object created will receive 5013.
• Exam Takeaway: Never use AutoNumber if your business domain legally requires strictly gapless sequential numbering (such as legal tax invoice numbers). Gapless numbering requires custom microflow logic utilizing pessimistic locking.

2. Decimal vs. Float: Financial Precision

• Float / Binary Floating Point: Approximates real numbers using binary floating-point representation (IEEE 754). It is susceptible to binary rounding artifacts (e.g., 0.1 + 0.2 = 0.30000000000000004). Never use Float for currency!
• Decimal (Fixed-Point Numeric): Implemented using exact fixed-point numeric representation (java.math.BigDecimal in Java, NUMERIC/DECIMAL in SQL). You configure exact precision (total digits) and scale (digits after the decimal point). Always use Decimal for currency, pricing, and tax calculations.

3. DateTime: Localized vs. Non-Localized

All DateTime attributes in Mendix are stored in the underlying SQL database as UTC timestamps. However, the Localize checkbox in Studio Pro fundamentally changes how that timestamp is interpreted and displayed:

4. Binary & System.FileDocument

Binary attributes store raw byte arrays. In modern Mendix architectures, rather than declaring raw binary attributes directly on custom entities, developers specialize System.FileDocument. The Mendix Runtime automatically manages the offloading of binary files to Amazon S3, Azure Blob Storage, or local disk, storing only file metadata (file name, size, upload date) in the relational database.

5. Enumerations vs. Reference Entities

• Enumerations: Fixed, compile-time list of static values (e.g., OrderStatus: [Draft, Submitted, Approved, Rejected]). Inexpensive, strongly typed, and support localized display captions. However, adding a new enumeration value requires a code change and application redeployment.
• Reference Entities (Lookup Tables): Used when the choice list must be dynamically maintained by business administrators at runtime without redeploying code (e.g., ProductCategories or CostCenters).

---

Database Indexing in Mendix: B-Tree Architecture & Query Acceleration

As persistable entity tables grow beyond tens of thousands of records, querying without indexes results in Full Table Scans—where the database engine must inspect every single row on disk from start to finish, causing exponential CPU spikes and sluggish page loads.

An Index creates an auxiliary B-Tree data structure that maps attribute values directly to row pointers, reducing query lookup complexity from a linear O(n) scan to a logarithmic O(log n) seek.

In Studio Pro, indexes are configured in the Indexes tab of the Entity properties dialog.

When to Apply Database Indexes

• Attributes frequently queried in XPath constraints (e.g., [Status = 'Active']).
• Attributes used as sorting columns in Data Grids or microflow retrieves (e.g., OrderDate DESC).
• Foreign key attributes involved in high-frequency table joins.
• High-cardinality search fields (e.g., EmailAddress, NationalIDNumber, InvoiceNumber).

The Write Penalty

Indexes are not free. While an index dramatically accelerates SELECT queries, every index on an entity introduces a write performance penalty:

• When a row is INSERTED, every index on that table must be updated.
• When an indexed attribute is UPDATED, the B-Tree node must be deleted and rebalanced.
• When a row is DELETED, references in all index trees must be pruned.
• Over-indexing a high-frequency write entity (such as an IoT sensor logging table) degrades insertion throughput and increases database disk consumption.

---

Single vs. Composite Indexes & The Leftmost Prefix Rule

Mendix supports both Single-Column Indexes and Composite (Multi-Column) Indexes.

A common mistake among developers is creating three separate single-column indexes on Status, Region, and OrderDate, expecting a query filtering on all three attributes to be three times faster. In reality, the database engine can typically utilize only one single-column index per table scan, resorting to an index merge or scanning the remainder in memory.

The Leftmost Prefix Rule for Composite Indexes

When you create a composite index in Studio Pro across multiple attributes, the exact order of columns in the index definition is paramount. Relational B-Tree indexes follow the Leftmost Prefix Rule:

Suppose you create a composite index on Order with columns in the exact order: (Status, Region, OrderDate).

Exam Rule of Thumb: When ordering attributes in a composite index, always place high-selectivity equality attributes first (e.g., TenantId, Status), and place range/sorting attributes last (e.g., CreatedDate, Amount).