Skip to content

Database Model#

Most of the settings and contents are saved in a Database Management System (DBMS) by i-doit. MySQL or MariaDB are applied as DBMS. But how is the structure of the i-doit database model?

Basics#

Some of the over 400 tables of the tenant database are structured with help of a defined names scheme. For example, this is the reason why all i-doit tables have the prefix "isys_".

Other identifiers for CMDB categories are "cats" for specific categories and "catg" for global categories. They end with the English name of the relevant category and the suffix _list (for example, isys_catg_model_list, isys_catg_memory_list). The tables category accounts for 50% of the complete database. Another major part of the tables are dialog lists with which combo boxes are filled. Many of these special lists contain values about type and manufacturer and can be recognized because of their names, such as "type" or "manufacturer".

References (Foreign Keys)#

To avoid redundancies within the data scheme the i-doit databases are structured in a relational way and normalized according to the scheme of the third normal form. Foreign keys have a uniform designation (see also the paragraph for "Name Scheme") so that you can recognize them immediately.

Name Scheme#

Besides the designation of the single tables, there is a consistent concept for names which runs through all field designations of the i-doit tables. It starts with the name of the table, followed by the actual name of the column. For example, the id field of the table isys_catg_cpu_list is called:

1
2
3
4
5
6
isys_catg_cpu_list__id
    /\              /\
 Table CPU            Column (ID)
isys_catg_cpu_list__title
    /\               /\
 Table CPU            Column(title)

Correspondingly, references to other fields (foreign keys) follow a similar principle. A reference always contains the complete name of the referenced column, so you can always see to which table column the reference points by means of the name.

Example object reference of a category: 

1
2
3
isys_catg_cpu_list__isys_obj__id
/\                 /\      /\
Table CPU      Table Obj.    Column

Objects#

Each object is a data record in the isys_obj table. Characteristics, such as the object type (servers, clients, routers etc.), creation date, status (normal, archived, deleted) and some additional attributes, are recorded in this table. The determination of the object type is carried out through referencing of the table isys_obj_type in which the respective characteristics and location (infrastructure, software etc.) are saved.

Global and Specific Categories#

Each category is reflected in the database as individual table and is in a 1:n relation to the object. As a basic principle there is a distinction between single values and lists. Single values are simple entries (for example, accounting or form factor) and only one entry per object exists in the table. On the other hand, lists enable multiple entries (CPU, memory, ports ...). In each data record of the global category there is a reference to a single object.

Example for the structure of the global category for a graphic card and global category for IP addresses:

isys_catg_graphic_list is above the foreign key isys_catg_graphic_list__isys_obj__id in relation to the object. Through the same scheme the IP address is in relation 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 for the determination of all objects, their graphic cards and IP details:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
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

The same also applies to specific categories. A query for all net details of all net objects looks as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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: Relation between categories and objects

Diagram: Relation between categories and objects **

In the diagram you can see how an object is linked to the global category CPU. This category again accesses Dialog+ entries, such as manufacturer and type. The same happens with the global category Form factor which accesses the form factor type.

Diagram: Relation between categories and objects

This diagram illustrates the relations of an object to the category "Host address" (IP). The object (for example, Server1) is in the the table isys_obj, the allocated IP addresses are in the table isys_catg_ip_list. Because of the detailed level of characteristics of the host address three additional tables are referenced beside the basic details (hostname, address ...):

  • isys_connection, containing the connection to the net object
  • isys_ip_assignment, determining the form of assignment of the IP (DHCP, static)
  • isys_net_type, providing the net type

Users, Contacts, Persons, Person Groups#

Contacts, which may consist both of internal users and external persons in i-doit, are recorded in the table isys_obj with the respective object types. Internal users differ from external "contacts" only by the filling of content with i-doit authentication parameters (such as user name or password) in the table isys_cats_person_list. Groups (and right groups) are saved in isys_obj by the system_._

Logbook#

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

  • isys_logbook_archive (archived logbook entries)
  • isys_logbook_event (events, such as created, changed or deleted)
  • isys_logbook_level (alarm level for coloured presentation in the logbook and priority)
  • isys_logbook_source (where was the event triggered, for example, internal or external source, Nagios etc.)

Overview of the Table#

CMDB categories

  • isysgui_catg
  • isysgui_cats
  • isysgui_custom

Dialog table for accounting (isys_catg_account_list)

  • isys_account

Tables for cable connections

  • 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: Type of the mobile contract

  • isys_cp_contract_type

Information about version and revision of the i-doit database

  • isys_db_init

Dependencies, type of dependency

  • isys_dependency
  • isys_dep_type

Connection 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 (group)
  • isys_file_physical (path to physical file)
  • isys_file_type (file types)

Group administration

  • isys_group

Connection table of the rights 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)

T

  • isys_location_image

Registration of locked objects (objects in process)

  • 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 time for maintenance contracts

  • isys_maintenance_reaction_rate

Dialog: Title of the working memory

  • isys_memory_title

Dialog: Title of the model

  • isys_model_title

Module administration

  • isys_module

Monitor

  • isys_monitor_resolution
  • isys_monitor_unit

Tables for the 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 Net (Layer 3-Net)

  • 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 connection 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 organisations

  • 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: Intended purpose

  • isys_purpose

Role and rights administration

  • 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, others)

  • 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 system: unit for air quantity

  • isys_ac_refrigerating_capacity_unit => Air-conditioning system: unit for cooling capacity

  • isys_guarantee_period_unit => Guarantee period: unit

  • isys_memory_unit => Memory: unit

  • isys_stor_unit => Storage: unit

  • isys_temp_unit => Temperature: unit

  • isys_ac_type => Air-conditioning system: 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: type of the connector

  • 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 => Graphic 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 => log. interface: type

  • isys_fc_port_type => FC port: type

  • isys_access_type => Access: access type