Database Model#

i-doit stores most content and settings in a Database Management System (DBMS). MySQL or MariaDB are used as the DBMS. But how is the database model of i-doit structured?

Fundamentals#

Some of the over 400 tables in the tenant database are structured according to a defined naming scheme. For example, all i-doit tables have the prefix "isys_".

CMDB categories follow with the identifier "cats" for specific and "catg" for global categories, ending with the English designation of the respective category and the suffix _list (e.g., isys_catg_model_list, isys_catg_memory_list). The category tables make up 50% of the entire database. An additional large portion of the tables are dialog lists, which are used to fill combo boxes. Many of these special lists contain values for type and manufacturer and are thus recognizable by names such as "type" or "manufacturer".

References (Foreign Keys)#

To avoid redundancy within the data schema, the i-doit databases are relationally structured and normalized according to the third normal form. Foreign keys have a consistent naming convention (see the section "Naming Scheme"), making them immediately apparent.

Naming Scheme#

In addition to the naming of individual tables, a consistent naming scheme runs through all field names of i-doit tables. It begins with the name of the table, followed by the actual column name. Thus, the ID field of the table isys_catg_cpu_list is, for example:

isys_catg_cpu_list__id
/\              /\
Table CPU            Column (ID)
isys_catg_cpu_list__title
/\               /\
Table CPU            Column (Title)

References to other fields (foreign keys) follow an analogous principle. A reference always includes the complete name of the referenced column, so the name always reveals which table column the reference points to.

Example of an object reference in a category:

isys_catg_cpu_list__isys_obj__id
/\                 /\      /\
Table CPU    Table Obj.   Column

Objects#

Every object is a record in the table isys_obj. This table stores properties such as the object type (server, client, router, etc.), the creation date, the status (Normal, Archived, Deleted), and some additional attributes. The object type is determined by referencing the table isys_obj_type, which contains the respective configuration and location (infrastructure, software, etc.).

Global and Specific Categories#

Each category is represented in the database as its own table and has a 1:n relationship to the object. A fundamental distinction is made between lists and single values. Single values are simple entries (e.g., Accounting or Form Factor), of which exactly one entry per object exists in the table. Lists, on the other hand, allow multiple entries (CPU, Memory, Ports, etc.). In each record of a global category, an individual object is referenced.

Example structure of the global category Graphics Card and the global category IP Addresses:

isys_catg_graphic_list is related to the object via the foreign key isys_catg_graphic_list__isys_obj__id. Via the same schema, the IP address is connected to the object: isys_catg_ip_list contains the foreign key isys_catg_ip_list__isys_obj__id.

This results in the following SQL statement to retrieve all objects with their graphics card and IP information:

SELECT
isys_obj__title,
isys_catg_graphic_list__title,
isys_catg_graphic_list__memory,
isys_memory_unit__title,
isys_catg_ip_list__hostname,
isys_catg_ip_list__address
FROM isys_obj
INNER JOIN isys_catg_graphic_list
ON isys_catg_graphic_list__isys_obj__id = isys_obj__id
INNER JOIN isys_memory_unit
ON isys_catg_graphic_list__isys_memory_unit__id = isys_memory_unit__id
INNER JOIN isys_catg_ip_list
ON isys_catg_ip_list__isys_obj__id = isys_obj__id

Specific categories behave the same way. The query for network information of all network objects would look as follows:

SELECT
isys_obj__title,
isys_cats_net_list__address,
isys_cats_net_list__dhcp_range_from,
isys_cats_net_list__dhcp_range_to,
isys_cats_net_list__dhcp,
isys_cats_net_list__def_gw
FROM
isys_cats_net_list
INNER JOIN isys_obj
ON isys_cats_net_list__isys_obj__id = isys_obj__id

Diagram: Relationship between categories and objects

** **

The graphic shows how an object is linked to the global category CPU. This category in turn accesses Dialog+ entries such as the manufacturer and the type. The same happens with the global category Form Factor, which accesses the form factor type.

This illustration explains the relationships of an object to the "Host Address" (IP) category. The object itself (e.g., server1) is located in the table isys_obj. The assigned IP addresses are in the table isys_catg_ip_list. Due to the detailed configuration depth of the host address, three additional tables are referenced besides the basic information (hostname, address, etc.):

isys_connection, which contains the connection to the network object
isys_ip_assignment, which determines the assignment type of the IP (DHCP, static)
isys_net_type, which returns the network type

Users, Contacts, Persons, Person Groups#

Contacts, which in i-doit can encompass both internal users and external persons, are stored in the table isys_obj with the corresponding object type. Internal users differ from external "contacts" only by the population of the i-doit authentication parameters (specifically: username, password) in the table isys_cats_person_list. Groups (and permission groups) are stored by the system in isys_obj.

Logbook#

The logbook consists of entries in the table isys_logbook. This table again contains some references to other tables. The following tables are important:

isys_logbook_archive (Archived log entries)
isys_logbook_event (Events such as Created, Changed, Deleted, etc.)
isys_logbook_level (Alarm level for color display in the logbook and urgency)
isys_logbook_source (Origin of the event, e.g., internal, external, Nagios, etc.)

Table Overview#

CMDB Categories

isysgui_catg
isysgui_cats
isysgui_custom

Dialog Table for Accounting (isys_catg_account_list)

isys_account

Cabling Tables

isys_cable_connection
isys_cable_type

Global Categories

isys_catg_access_list
isys_catg_accounting_list
isys_catg_application_list
isys_catg_backup_list
isys_catg_connector_list
isys_catg_contact_list
isys_catg_controller_list
isys_catg_cpu_frequency
isys_catg_cpu_list
isys_catg_distributor
isys_catg_drive_list
isys_catg_emergency_plan_list
isys_catg_fc_port_list
isys_catg_file_list
isys_catg_formfactor_list
isys_catg_global_category
isys_catg_global_list
isys_catg_graphic_list
isys_catg_idic_list
isys_catg_image_list
isys_catg_ip_list
isys_catg_ip_list_2_isys_catg_port_list
isys_catg_ip_list_2_isys_netp_ifacel
isys_catg_lic_list
isys_catg_location_list
isys_catg_logb_list
isys_catg_maintenance_list
isys_catg_manual_list
isys_catg_memory_list
isys_catg_model_list
isys_catg_nagios
isys_catg_nagios_list
isys_catg_netp_list
isys_catg_netv
isys_catg_netv_list
isys_catg_net_type_list
isys_catg_odep_list
isys_catg_overview_list
isys_catg_port_list
isys_catg_port_list_2_isys_netp_con
isys_catg_port_list_2_isys_netp_ifacel
isys_catg_power_consumer_list
isys_catg_sanpool_list
isys_catg_sound_list
isys_catg_stor_list
isys_catg_ui_list
isys_catg_version_list
isys_catg_virtual_list
isys_catg_virtual_machine_list
isys_catg_workflow_list

Specific Categories

isys_cats_access_point_list
isys_cats_ac_list
isys_cats_application_list
isys_cats_building_list
isys_cats_client_list
isys_cats_cp_contract_list
isys_cats_distributor
isys_cats_emergency_plan_list
isys_cats_enclosure_list
isys_cats_file_list
isys_cats_group_list
isys_cats_lic_group
isys_cats_lic_item
isys_cats_lic_list
isys_cats_location_generic_list
isys_cats_maintenance_list
isys_cats_monitor_list
isys_cats_net_list
isys_cats_pobj_list
isys_cats_prt_emulation
isys_cats_prt_list
isys_cats_prt_paper
isys_cats_room_list
isys_cats_router_list
isys_cats_san_list
isys_cats_service_list
isys_cats_switch_fc_list
isys_cats_switch_net_list
isys_cats_tapelib_list
isys_cats_wan_list
isys_cats_ws_net_type_list

Connections between Objects and Categories

isys_connection

Contact References

isys_contact
isys_contact_data_item
isys_contact_type

Object Combinations

isys_container

Dialog: Mobile Contract Type

isys_cp_contract_type

Information about Version and Revision of the i-doit Database

isys_db_init

Dependencies, Dependency Type

isys_dependency
isys_dep_type

Link Table for Drive to Storage

isys_drive_list_2_stor_list

Export Templates

isys_export

Dialog: FC Port Medium

isys_fc_port_medium

Files

isys_file_category (Category)
isys_file_group (Grouping)
isys_file_physical (Path to physical file)
isys_file_type (File types)

Group Management

isys_group

Link Table of Permission Groups to Modules, Objects, Contacts, or User Sessions

isys_group_2_isys_module
isys_group_2_isys_obj
isys_group_2_isys_person_intern
isys_group_2_isys_user_session

Dialog: IP Assignment

isys_ip_assignment

LDAP Configuration

isys_ldap (Connection to LDAP server)
isys_ldap_directory (Directory types)

Location Image

isys_location_image

Registration of Locked Objects (Objects being edited)

isys_lock

Logbook

isys_logbook
isys_logbook_archive
isys_logbook_event
isys_logbook_event_class
isys_logbook_lc_parameter
isys_logbook_level
isys_logbook_source

Dialog: Reaction Times for Maintenance Contracts

isys_maintenance_reaction_rate

Dialog: Memory Name

isys_memory_title

Dialog: Model Designation

isys_model_title

Module Management

isys_module

Monitor

isys_monitor_resolution
isys_monitor_unit

Tables for Nagios Configuration

isys_nagios_commands
isys_nagios_config
isys_nagios_contacts
isys_nagios_contact_groups
isys_nagios_dependency
isys_nagios_host_escalations
isys_nagios_nagioshosts
isys_nagios_ndo
isys_nagios_service
isys_nagios_service_escalations
isys_nagios_timeperiods

Dialogs for Specific Category Network (Layer 3 Network)

isys_net_dns_domain
isys_net_dns_server
isys_net_type
isys_net_type_title

Table for CMDB Objects (Configuration Items)

isys_obj

Table for CMDB Object Types (CI Types)

isys_obj_type

Object Type Group and Link Table of Object Types to Global Categories

isys_obj_type_2_isysgui_catg
isys_obj_type_2_isysgui_catg_overview
isys_obj_type_group

Table for Organizations

isys_organisation_intern_iop

Table for Contacts

isys_person_extern
isys_person_intern
isys_person_intern_iop

Port Dialogs

isys_port_duplex
isys_port_negotiation
isys_port_speed
isys_port_standard

Power Connection

isys_power_con
isys_power_female_socket
isys_power_fuse_ampere
isys_power_male_plug

Category General: Purpose

isys_purpose

Role and Permission Management

isys_right
isys_right_2_isys_role
isys_role

SAN Capacity Unit

isys_san_capacity_unit

Search

isys_search

Service

isys_service_manufacturer

RAID Level

isys_stor_raid_level

Tape Library

isys_tapelib_type

Object Tree Groups (Infrastructure, Software, Other)

isys_tree_group

Time Unit

isys_unit_of_time

User Locales, Sessions, and Settings

isys_user_locale
isys_user_mydoit
isys_user_session
isys_user_setting
isys_user_ui

WAN

isys_wan_capacity_unit
isys_wan_role
isys_wan_type

WiFi Specifications

isys_wf_type_2_wf_tp
isys_wlan_auth
isys_wlan_channel
isys_wlan_encryption
isys_wlan_function
isys_wlan_standard

Tables for Workflow Management

isys_workflow
isys_workflow_2_isys_workflow_action
isys_workflow_action
isys_workflow_action_parameter
isys_workflow_action_type
isys_workflow_category
isys_workflow_status
isys_workflow_template_parameter
isys_workflow_type

Temporary Tables

temp_obj_data

Dialog Plus Tables#

isys_ac_air_quantity_unit => Air conditioning: Air volume unit
isys_ac_refrigerating_capacity_unit => Air conditioning: Cooling capacity unit
isys_guarantee_period_unit => Warranty period: Unit
isys_memory_unit => Memory: Unit
isys_stor_unit => Storage: Unit
isys_temp_unit => Temperature: Unit
isys_ac_type => Air conditioning: Type
isys_catd_drive_type => Drive: Type
isys_catg_cpu_type => CPU: Type
isys_catg_formfactor_type => Form Factor
isys_cats_prt_type => Printer: Type
isys_client_type => Client: Type
isys_controller_type => Controller: Type
isys_filesystem_type => File System
isys_memory_type => Memory: Type
isys_monitor_type => Monitor: Type
isys_port_type => Port: Type
isys_power_fuse_type => Power Object: Fuse Type
isys_power_connection_type => Power Object: Socket Type
isys_room_type => Room: Type
isys_stor_con_type => Storage: Connection
isys_stor_type => Storage: Device Type
isys_ui_con_type => Interface: Connection Type
isys_ui_plugtype => Interface: Plug Type
isys_application_manufacturer => Applications: Manufacturer
isys_catg_cpu_manufacturer => CPU: Manufacturer
isys_controller_manufacturer => Controller: Manufacturer
isys_graphic_manufacturer => Graphics Cards: Manufacturer
isys_memory_manufacturer => Memory: Manufacturer
isys_model_manufacturer => Model: Manufacturer
isys_sound_manufacturer => Sound Cards: Manufacturer
isys_stor_manufacturer => Storage: Manufacturer
isys_controller_model => Controller: Model
isys_stor_model => Storage: Model
isys_depth_unit => Form Factor: Unit
isys_iface_manufacturer => Interface: Manufacturer
isys_iface_model => Interface: Model
isys_pc_manufacturer => Consumer: Manufacturer
isys_pc_model => Consumer: Model
isys_plug_type => Port: Plug
isys_netx_ifacel_type => Logical Interface: Type
isys_fc_port_type => FC Port: Type
isys_access_type => Access: Access Type