MySQL example database design: A Nagios MySQL database design

MySQL/MariaDB FAQ: Can you provide a large, complete, example MySQL database design?

Sure. Once upon a time I worked on a project where I wrote a web-based user interface that would let “everyday users” maintain their own Nagios configuration data. After logging in to the web interface, users could modify the Nagios configuration as desired, for instance, when they added new equipment to their network, to change notifications, etc.

Because everything worked out really well, I thought I’d share my Nagios/MySQL database schema here in case it will help anyone else.

Notes about the example Nagios database and the UI design

To generate the Nagios configuration files from the web interface and the data stored in the MySQL database, I provided a “Generate” button that would validate the configuration, backup the previous Nagios configuration files, write out the new Nagios configuration files, and restart the Nagios service. I thought it might be a little clunky having to go through this "generate" process, but I found some decent ways of showing when the database was out of sync with the Nagios plain-text configuration files, and I thought the end result was pretty good.

I ended up writing the user interface using Java and JSF, but that doesn't really matter; I could have written it in any language. The important thing is how you treat null fields in the database, and being able to code "inheritance" in your Nagios objects. (Oops, I forgot about that. I found a decent way to show inheritance in the UI, which I was also pretty happy with.)

I created this database schema for Nagios version 3.x.

The database design isn't 100% complete. I didn't design the tables I didn't think we really needed. I thought I'd add those later as needed, but so far we haven't needed them.

As mentioned, this database schema worked great for my web-based user interface. The ability to handle null fields and inheritance were really the keys to developing the user interface.

Potential changes

I was really happy with how this project worked out, and I wouldn't change much of anything in the database, other than finishing it for the elements I intentionally skipped. The only other thing I can think of is that I might add "versioning" to the database design, but that's just a wish-list item ... I thought this might be a problem, but it hasn't been. As I thought about it, if somebody really messed something up we should be able to restore the database from a backup.

Example MySQL database design: A Nagios MySQL database schema

This schema is very long, so that's why I've included it here at the end. Again, this is a Nagios 3.x database schema, created for use with a MySQL database:

#---------------------------------------------( users )-------------------------------------------------#

drop table if exists users;

# TODO: verify that this constraint creates a true index for performance
create table users (
  id int auto_increment not null,
  username varchar(32) not null,
  password varchar(16) not null,                  # this will be changed later to add encryption
  primary key (id),
  constraint unique index idx_users_unique (username)
) ENGINE = InnoDB;


#---------------------------------------------( commands )-------------------------------------------------#

drop table if exists commands;
create table commands (
  id int auto_increment not null,
  command_name varchar(128) not null,
  command_line varchar(1024) not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  primary key (id),
  constraint unique index idx_commands_cmd_name_unique (command_name)
) ENGINE = InnoDB;


#--------------------------------------------( time periods )------------------------------------------------#

drop table if exists time_periods;
create table time_periods (
  id int auto_increment not null,
  timeperiod_name varchar(128) not null,
  alias varchar(128),
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  primary key (id),
  constraint unique index idx_time_periods_name_unique (timeperiod_name)
) ENGINE = InnoDB;

#
# TODO -- add a sequence_number to these so they can be re-displayed in the order
#         they were specified. this is not too important until/unless these are 
#         managed in the ui.
drop table if exists timeperiod_values;
create table timeperiod_values (
  id int auto_increment not null,
  timeperiod_id int not null,
  datestring varchar(256) not null,                        # "sunday", or "monday 3", or "july 10 - 15"
  timerange varchar(256) not null,                         # a valid timerange, like "00:00-24:00"
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (timeperiod_id) references time_periods(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_timeperiod_values_unique (timeperiod_id, datestring, timerange)
) ENGINE = InnoDB;

# to view the entries
#select timeperiod_name, datestring, timerange
#from time_periods, timeperiod_values
#where time_periods.id = timeperiod_values.timeperiod_id
#order by timeperiod_name, datestring;


#----------------------------------( contacts, contact_groups )-------------------------------------------#

drop table if exists contacts;
create table contacts (
  id int auto_increment not null,
  contact_name varchar(128) not null,                   # short name
  contact_source char(1) not null default 'a',          # 'a' is for acme
  alias varchar(128),
  host_notifications_enabled boolean,
  service_notifications_enabled boolean,
  host_notification_period int,                         # FK to timeperiods
  service_notification_period int,                      # FK to timeperiods
  host_notification_option_down boolean,                # d
  host_notification_option_unreachable boolean,         # u
  host_notification_option_recoveries boolean,          # r
  host_notification_option_flapping boolean,            # f
  host_notification_option_scheduled boolean,           # s
  host_notification_option_none boolean,                # n
  service_notification_option_warning boolean,          # w
  service_notification_option_unknown boolean,          # u
  service_notification_option_critical boolean,         # c
  service_notification_option_recoveries boolean,       # r
  service_notification_option_flapping boolean,         # f
  #service_notification_option_scheduled boolean,       # s (conflict in docs though)
  service_notification_option_none boolean,             # n
  email varchar(256),
  pager varchar(20),
  address1 varchar(1024),
  address2 varchar(1024),
  address3 varchar(1024),
  address4 varchar(1024),
  can_submit_commands boolean,
  retain_status_information boolean,
  retain_nonstatus_information boolean,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (host_notification_period) references time_periods(id),
  foreign key (service_notification_period) references time_periods(id),
  primary key (id),
  constraint unique index idx_contacts_name_unique (contact_name)
) ENGINE = InnoDB;

create index contacts_idx on contacts(contact_name);

drop table if exists contact_groups;
create table contact_groups (
  id int auto_increment not null,
  contact_group_name varchar(128) not null,            # short name
  contact_group_source char(1) not null default 'a',   # 'a' is for acme
  alias varchar(128),
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  primary key (id),
  constraint unique index contactgroups_name_unique (contact_group_name)
) ENGINE = InnoDB;

create index contact_groups_idx on contact_groups(contact_group_name);

# records can either be "host notification commands" or "service notification commands".
# this used to be two identical tables (with different names, host, and service) so i merged them to this
# one table.

drop table if exists contact_notification_commands;
create table contact_notification_commands (
  id int auto_increment not null,
  contact_id int not null,
  command_id int not null,
  notification_type char(1) not null,                     # 'h' for host, 's' for service
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (contact_id) references contacts(id) ON DELETE CASCADE,
  foreign key (command_id) references commands(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_contact_notif_unique (contact_id, command_id, notification_type)
) ENGINE = InnoDB;

# a contactgroup has 1+ contacts,
# and a contact can be in many contact groups

drop table if exists contactgroup_contact_members;
create table contactgroup_contact_members (
  id int auto_increment not null,
  contactgroup_id int not null,
  contact_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (contactgroup_id) references contact_groups(id) ON DELETE CASCADE,
  foreign key (contact_id) references contacts(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_contactgroup_members_unique (contactgroup_id, contact_id)
) ENGINE = InnoDB;

# a contactgroup can contain other contact groups
drop table if exists contactgroup_contactgroup_members;
create table contactgroup_contactgroup_members (
  id int auto_increment not null,
  contactgroup_id int not null,
  contactgroup_member_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (contactgroup_id) references contact_groups(id) ON DELETE CASCADE,
  foreign key (contactgroup_member_id) references contact_groups(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_contactgroup_cgmembers_unique (contactgroup_id, contactgroup_member_id)
) ENGINE = InnoDB;


#---------------------------------------------( hosts )-------------------------------------------------#

drop table if exists hosts;
create table hosts (
  id int auto_increment not null,
  is_template boolean not null,             # make it easier to find templates
  name        varchar(128),                 # template name
  register    int,                          # 0 == do not register
  host_name varchar(128),                   # the "short name"
  alias varchar(128),                       # a longer name, or description
  use_id int,                               # the name of template to use
  display_name varchar(128),
  ip_address varchar(20),
  check_command_id int,                     # shortname of the command; if blank host won't be checked
  check_command_parameters varchar(1024),
  initial_state_up boolean,
  initial_state_down boolean,
  initial_state_unreachable boolean,
  max_check_attempts int,                   # this may need to be "not null"
  check_interval int,
  retry_interval int,
  active_checks_enabled boolean,
  passive_checks_enabled boolean,
  check_period_id int,                      # FK to time_periods
  obsess_over_host boolean,
  check_freshness boolean,
  freshness_threshold int,
  event_handler_id int,                     # FK to command_name
  event_handler_enabled boolean,
  event_handler_parameters varchar(1024),
  low_flap_threshold int,
  high_flap_threshold int,
  flap_detection_enabled boolean,
  #flap_detection_options,
  flap_detection_option_up boolean,
  flap_detection_option_down boolean,
  flap_detection_option_unreachable boolean,
  process_perf_data boolean,
  retain_status_information boolean,
  retain_nonstatus_information boolean,
  #contacts (TODO: FK),
  #contact_groups (TODO: FK),
  notification_interval int,
  first_notification_delay int,
  notification_period_id int,                    # FK to time_periods
  #notification_options (down/unreach/recoveries/flapping),
  notification_option_down boolean,              # d
  notification_option_unreachable boolean,       # u
  notification_option_recoveries boolean,        # r 
  notification_option_flapping boolean,          # f
  notification_option_scheduled boolean,         # s
  notification_option_none boolean,              # n
  notifications_enabled boolean,
  # stalking_options,
  stalking_option_up boolean,                    # o
  stalking_option_down boolean,                  # d
  stalking_option_unreachable boolean,           # u
  notes text,
  notes_url varchar(1024),
  action_url varchar(1024),
  icon_image varchar(1024),
  icon_image_alt varchar(1024),
  vrml_image varchar(1024),
  statusmap_image varchar(1024),
  2d_coord_x int,
  2d_coord_y int,
  3d_coord_x float,
  3d_coord_y float,
  3d_coord_z float,
  updated_by_id int,
  updated_time timestamp not null default now(),
  primary key (id),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (use_id) references hosts(id) ON DELETE SET NULL,
  foreign key (check_command_id) references commands(id) ON DELETE SET NULL,
  foreign key (check_period_id) references time_periods(id) ON DELETE SET NULL,
  foreign key (notification_period_id) references time_periods(id) ON DELETE SET NULL,
  foreign key (event_handler_id) references commands(id) ON DELETE SET NULL,
  constraint unique index idx_host_name_unique (name),                 # template names must be unique
  constraint unique index idx_host_hostname_unique (host_name)         # host names must be unique
) ENGINE = InnoDB;
# can't currently find in nagios-3 docs
#template_name
#checks_enabled
#failure_prediction_enabled

create index hosts_hostname_idx on hosts(host_name);
create index hosts_name_idx on hosts(name);



# a host can have 1+ parents (check this)
drop table if exists host_parents;
create table host_parents (
  id int auto_increment not null,
  host_id int not null,
  parent_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (host_id) references hosts(id) ON DELETE CASCADE,
  foreign key (parent_id) references hosts(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_host_parents_unique (host_id, parent_id)
) ENGINE = InnoDB;

# don't think this table is needed
# i think the information needs to be gotten from the
# hostgroup_host_members table
#create table host_groups_for_host (
#  id int auto_increment not null,
#  host_id int not null,
#  host_group_id int not null,
#  display_order int not null
#  foreign key (host_id) references hosts(id),
#  foreign key (host_group_id) references host_groups(id),
#);

drop table if exists host_contacts;
create table host_contacts (
  id int auto_increment not null,
  host_id int not null,
  contact_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (host_id) references hosts(id) ON DELETE CASCADE,
  foreign key (contact_id) references contacts(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_host_contacts_unique (host_id, contact_id)
) ENGINE = InnoDB;

drop table if exists host_contact_groups;
create table host_contact_groups (
  id int auto_increment not null,
  host_id int not null,
  contact_group_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (host_id) references hosts(id) ON DELETE CASCADE,
  foreign key (contact_group_id) references contact_groups(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_host_contact_groups_unique (host_id, contact_group_id)
) ENGINE = InnoDB;


#---------------------------------------------( host groups )-------------------------------------------------#

drop table if exists host_groups;
create table host_groups (
  id int auto_increment not null,
  hostgroup_name varchar(128) not null,    # the "short name"
  alias varchar(128),
  #host_members (FK)
  #host_group_members (FK)
  notes text,
  notes_url varchar(1024),
  action_url varchar(1024),
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  primary key (id),
  constraint unique index idx_host_groups_unique (hostgroup_name)
) ENGINE = InnoDB;

# hostgroups have one or more hosts
drop table if exists hostgroup_host_members;
create table hostgroup_host_members (
  id int auto_increment not null,
  hostgroup_id int not null,
  host_id int not null,
  #date_updated timestamp not null default now(),
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (hostgroup_id) references host_groups(id) ON DELETE CASCADE,
  foreign key (host_id) references hosts(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_hostgroup_host_members_unique (hostgroup_id, host_id)
) ENGINE = InnoDB;


# hostgroups can have other host_groups
# not sure about supporting this
drop table if exists hostgroup_hostgroup_members;
create table hostgroup_hostgroup_members (
  id int auto_increment not null,
  hostgroup_id int not null,
  hostgroup_member_id int not null,
  #date_updated timestamp not null default now(),
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (hostgroup_id) references host_groups(id) ON DELETE CASCADE,
  foreign key (hostgroup_member_id) references host_groups(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_hostgroup_hostgroup_members_unique (hostgroup_id, hostgroup_member_id)
) ENGINE = InnoDB;


#------------------------------------( services, service groups )-----------------------------------------------#

# TODO: need to add templates to services as well

drop table if exists services;
create table services (
  id int auto_increment not null,
  is_template boolean not null,
  name varchar(128),
  register int,
  use_id int,
  service_description varchar(128),
  display_name varchar(128),
  is_volatile boolean,
  check_command_id int,                            # FK to commands (not null?)
  check_command_parameters varchar(1024),
  initial_state_ok boolean,                        # o
  initial_state_warning boolean,                   # w
  initial_state_unknown boolean,                   # u
  initial_state_critical boolean,                  # c
  max_check_attempts int,
  check_interval int,
  retry_interval int,
  active_checks_enabled boolean,
  passive_checks_enabled boolean,
  check_period_id int,                             # FK to timeperiods
  obsess_over_service boolean,
  check_freshness boolean,
  freshness_threshold int,
  event_handler_id int,                            # FK to commands
  event_handler_enabled boolean,
  event_handler_parameters varchar(1024),
  low_flap_threshold int,
  high_flap_threshold int,
  flap_detection_enabled boolean,
  flap_detection_option_ok boolean,                # o
  flap_detection_option_warning boolean,           # w
  flap_detection_option_critical boolean,          # c
  flap_detection_option_unknown boolean,           # u
  process_perf_data boolean,
  retain_status_information boolean,
  retain_nonstatus_information boolean,
  notification_interval int,
  first_notification_delay int,
  notification_period_id int,                      # FK to timeperiods
  notification_option_ok boolean,                  # o
  notification_option_warning boolean,             # w 
  notification_option_unknown boolean,             # u
  notification_option_critical boolean,            # c
  notification_option_recoveries boolean,          # r
  notification_option_flapping boolean,            # f
  notification_option_scheduled boolean,           # s
  notification_option_none boolean,                # n
  notifications_enabled boolean,
  stalking_option_ok boolean,                      # o
  stalking_option_warning boolean,                 # w
  stalking_option_unknown boolean,                 # u
  stalking_option_critical boolean,                # c
  notes text,
  notes_url varchar(1024),
  action_url varchar(1024),
  icon_image varchar(1024),
  icon_image_alt varchar(1024),
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (use_id) references services(id) ON DELETE SET NULL,
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (check_command_id) references commands(id) ON DELETE SET NULL,
  foreign key (check_period_id) references time_periods(id) ON DELETE SET NULL,
  foreign key (event_handler_id) references commands(id) ON DELETE SET NULL,
  foreign key (notification_period_id) references time_periods(id) ON DELETE SET NULL,
  constraint unique index idx_service_displayname_unique (display_name),
  primary key (id),
  constraint unique index idx_service_unique (service_description)
  # TODO: determine what needs to be unique here
  #constraint services_unique unique (host_id, service_description)
) ENGINE = InnoDB;

drop table if exists service_groups;
create table service_groups (
  id int auto_increment not null,
  servicegroup_name varchar(128) not null,     # short name
  alias varchar(128),
  #service_members (TODO: FK)
  #service_group_members (TODO: FK)
  notes text,
  notes_url varchar(1024),
  action_url varchar(1024),
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  primary key (id),
  constraint unique index idx_service_groups_unique (servicegroup_name)
) ENGINE = InnoDB;

drop table if exists service_contacts;
create table service_contacts (
  id int auto_increment not null,
  service_id int not null,
  contact_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (service_id) references services(id) ON DELETE CASCADE,
  foreign key (contact_id) references contacts(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_service_contacts_unique (service_id, contact_id)
) ENGINE = InnoDB;

drop table if exists service_contactgroups;
create table service_contactgroups (
  id int auto_increment not null,
  service_id int not null,
  contactgroup_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (service_id) references services(id) ON DELETE CASCADE,
  foreign key (contactgroup_id) references contact_groups(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_service_contactgroups_unique (service_id, contactgroup_id)
) ENGINE = InnoDB;

drop table if exists service_host_members;
create table service_host_members (
  id int auto_increment not null,
  service_id int not null,
  host_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (service_id) references services(id) ON DELETE CASCADE,
  foreign key (host_id) references hosts(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_service_host_members_unique (service_id, host_id)
) ENGINE = InnoDB;

drop table if exists service_hostgroup_members;
create table service_hostgroup_members (
  id int auto_increment not null,
  service_id int not null,
  hostgroup_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (service_id) references services(id) ON DELETE CASCADE,
  foreign key (hostgroup_id) references host_groups(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_service_hostgroup_members_unique (service_id, hostgroup_id)
) ENGINE = InnoDB;


#------------------------------------------( service groups )----------------------------------------------#

# servicegroups can have 1+ services
drop table if exists servicegroup_service_members;
create table servicegroup_service_members (
  id int auto_increment not null,
  servicegroup_id int not null,
  service_id int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (servicegroup_id) references service_groups(id) ON DELETE CASCADE,
  foreign key (service_id) references services(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_servicegroup_svc_members_unique (servicegroup_id, service_id)
) ENGINE = InnoDB;

# servicegroups can have 1+ other service groups
# not sure about supporting this
drop table if exists servicegroup_servicegroup_members;
create table servicegroup_servicegroup_members (
  id int auto_increment not null,
  servicegroup_id int not null,
  servicegroup_member_id int not null,
  display_order int not null,
  updated_by_id int,
  updated_time timestamp not null default now(),
  foreign key (updated_by_id) references users(id) ON DELETE SET NULL,
  foreign key (servicegroup_id) references service_groups(id) ON DELETE CASCADE,
  foreign key (servicegroup_member_id) references service_groups(id) ON DELETE CASCADE,
  primary key (id),
  constraint unique index idx_servicegroup_svcgroup_members_unique  (servicegroup_id, servicegroup_member_id)
) ENGINE = InnoDB;