Database Structure

This page was automatically generated from the database schema dump and may be incomplete or incorrect.

This section documents the database structure of the enaio® system — which tables exist in the database schema, which columns they carry, and how the recurring patterns of the object, stamm and register tables relate to each other. The schema is database-independent — enaio® supports several relational database backends (e.g. Microsoft SQL Server, Oracle). Data types in this documentation are given in SQL Server notation (int, nvarchar, datetime, bigint, smallint, real, image); the Oracle equivalents are NUMBER, NVARCHAR2, DATE, NUMBER(19), NUMBER(5), BINARY_FLOAT and BLOB respectively.

The structures described here are the immediate physical storage form; they are generally not modified directly (except via ado.ExecuteSQL scripts for administrative corrections), but through the DMS, Standard and Workflow engines. This documentation is intended as a reference for administrators, report developers and integration developers.

1. Schema Conventions

The database uses a mix of fixed system tables and type-specific tables per object type. The following conventions are applied throughout:

1.1. Suffix scheme

For every configured DMS object type, several tables exist whose names carry a sequential number X. X corresponds to the main type (haupttyp column) + 1 — object1 thus contains objects with haupttyp = 0, object17 those with haupttyp = 16. The full object-type ID (haupttyp * 65536 + untertyp) is described in Object-type ID.

Name Content

objectX / stammX / registerX

Main object table. Holds one row per object of type X. Contains common system fields (id, timestamp, status, permission IDs, …) plus type-specific index fields.

objectXr / stammXr / registerXr

Rights table. Holds per-object permission entries (complementary to the global security system via ossd). Identical 4-column structure across all object types.

objectXs / stammXs / registerXs

Shadow table for index-data history. Holds the previous index-data state before each change. Only osguid is common (snapshot identifier); after it follow the type-specific index fields.

objectXlistY / stammXlistY / registerXlistY

Table-field data. One table per table-field Y defined on object type X (grid field); holds one row per grid row. Common fields: id (FK to the main object) and line (row number in the grid).

objectXlistYs / stammXlistYs / registerXlistYs

Shadow variant for table fields. Common: osguid and line.

1.2. Column-name convention

Index fields follow a historically grown, type-dependent positional notation (Hungarian style):

  • feld1, feld2, … — text fields (nvarchar)

  • zahl1, zahl2, … — integer fields (int / smallint)

  • datum1, datum2, … — date fields (datetime)

  • real1, real2, … — decimal / floating-point fields (decimal / real)

The mapping "column name → semantic index field" is configured per object type and accessible via the DMS object definition (dms.GetObjDef).

System fields, on the other hand, use German-language descriptive names (anleger, angelegt, archiviert, zeitstempel, medium_doc, …) — a historical legacy.

2. Hierarchical object types

The following three pages describe the pattern — common fields, rights, shadow and table-field variants — and not individual numbered tables.

3. Users and groups

4. Portfolios and document assignments

  • mappe — portfolios.

  • mdrel — portfolio-document assignment.

5. Media and archive

  • medien — storage media (archive media).

  • medrel — media-set relations.

  • archex — archive extensions / server assignment.

6. Locks and links

  • doclock — document locks (checkout).

  • hyplnk — hyperlinks (cross-references between objects).

  • combined — combined documents (parent/child relations).

7. Annotations

8. External systems

9. Audit and history

10. Sessions, devices and locks

11. Permissions and profiles

The workflow organisation objects (osorg*) are documented under Workflows.

12. System events

13. DMS object types, index fields and structures

14. Versions, hashes and signatures

15. Annotations and collaboration

16. Portfolios (OS variant)

17. Remarks and texts

18. Full-text search

19. Licenses and cryptography

20. Localisation and icons

21. Server cluster and configuration

22. Active editing and temporary state

23. Subscriptions and revisits (OS backing)

24. Workflows

The workflow engine stores models, running processes, history and management data in dedicated tables prefixed oswf. History tables additionally carry the prefix oswfh. The workflow organisation (users, roles, departments, classes and attributes) lives in the osorg* tables and is used both by the workflow engine and by the DMS security system.

Models and definitions

Masks and list UI

Processes and activities (runtime)

Organisation and performers

Workflow-specific performer assignments:

Organisation master data (osorg*) — users, roles, departments, classes, attributes:

Timers

Routing lists

Reports

History (oswfh*)

25. Laboratory (LIMS)

Tables of the laboratory-data integration (Laboratory Master/Transaction Tables):

26. Medicine (HL7)

Tables of the medical-data integration (Medical Master/Transaction Tables):