Working with Multi-Select & Dropdowns and SQL

[eluser]Philipp Gérard[/eluser]
This would be a normalized database layout reflecting your structure. I wasn't sure about varchar(ticket), if this is also an entry that can be external, then you should also normalize this field.

Code:

CREATE TABLE `outages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket` varchar(128) DEFAULT NULL,
  `starttime` datetime DEFAULT NULL,
  `endtime` datetime DEFAULT NULL,
  `summary` varchar(128) DEFAULT NULL,
  `details` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `outages_locations` (
  `outage_id` int(11) DEFAULT NULL,
  `location_id` int(11) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `outages_services` (
  `outage_id` int(11) DEFAULT NULL,
  `service_id` int(11) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Exemplary content:

Code:

INSERT INTO `outages` (`id`, `ticket`, `starttime`, `endtime`, `summary`, `details`) VALUES (1, 'ABC123', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'My summary', 'My details');
INSERT INTO `services` (`id`, `name`) VALUES (1, 'Example service');
INSERT INTO `locations` (`id`, `name`) VALUES (1, 'Boston');
INSERT INTO `outages_locations` (`outage_id`, `location_id`) VALUES (1, 1);
INSERT INTO `outages_services` (`outage_id`, `service_id`) VALUES (1, 1);

(1 outage that is connected to 1 location and 1 service by the outages_locations/outages_services tables)