The following MySQL structure should normalize normally. This will make your queries a little harder to write for some cases, but it will make the application more powerful and able to grow exponentially without sacrificing performance. We have a large MySQL database with many related tables that contain foreign keys for people to various interviews, notes and other data that are awesome! One point is that if you use a group as the name of a table, remember to use `` mark like:
`group`
Thus, MySQL does not try to invalidate the INNER JOIN group ON (foo=bar) and wait for GROUP BY . You will also have to put restrictions on the front of the application, which prevents the device from being added without a parent if that is the desired goal. But it is not so difficult to do. In any case, look at the examples and enjoy experimenting / programming!
Online Demo: http://www.sqlfiddle.com/#!2/e9e94/2/0
Here is the proposed MySQL table structure with the least amount of data to account for one instance of each necessary case from your question: copy and paste in the .sql file and import into an empty database using phpMyAdmin
-- phpMyAdmin SQL Dump -- version 3.5.2.2 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 -- Generation Time: Jun 07, 2013 at 08:14 PM -- Server version: 5.5.27 -- PHP Version: 5.4.7 SET FOREIGN_KEY_CHECKS=0; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @ OLD_CHARACTER_SET_CLIENT=@ @CHARACTER_SET_CLIENT */; /*!40101 SET @ OLD_CHARACTER_SET_RESULTS=@ @CHARACTER_SET_RESULTS */; /*!40101 SET @ OLD_COLLATION_CONNECTION=@ @COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `stackoverflow` -- -- -------------------------------------------------------- -- -- Table structure for table `device` -- CREATE TABLE IF NOT EXISTS `device` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; -- -- Dumping data for table `device` -- INSERT INTO `device` (`id`) VALUES (1), (2), (3), (4), (5), (6); -- -------------------------------------------------------- -- -- Table structure for table `group` -- CREATE TABLE IF NOT EXISTS `group` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ; -- -- Dumping data for table `group` -- INSERT INTO `group` (`id`) VALUES (1), (2), (3), (4), (5), (6), (7), (8); -- -------------------------------------------------------- -- -- Table structure for table `groups_have_devices` -- CREATE TABLE IF NOT EXISTS `groups_have_devices` ( `group_id` int(11) NOT NULL, `device_id` int(11) NOT NULL, PRIMARY KEY (`group_id`,`device_id`), KEY `device_id` (`device_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `groups_have_devices` -- INSERT INTO `groups_have_devices` (`group_id`, `device_id`) VALUES (4, 6); -- -------------------------------------------------------- -- -- Table structure for table `groups_have_people` -- CREATE TABLE IF NOT EXISTS `groups_have_people` ( `group_id` int(11) NOT NULL, `person_id` int(11) NOT NULL, PRIMARY KEY (`group_id`,`person_id`), KEY `person_id` (`person_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `groups_have_people` -- INSERT INTO `groups_have_people` (`group_id`, `person_id`) VALUES (1, 2), (5, 5); -- -------------------------------------------------------- -- -- Table structure for table `groups_have_sites` -- CREATE TABLE IF NOT EXISTS `groups_have_sites` ( `group_id` int(11) NOT NULL, `site_id` int(11) NOT NULL, PRIMARY KEY (`group_id`,`site_id`), KEY `site_id` (`site_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `groups_have_sites` -- INSERT INTO `groups_have_sites` (`group_id`, `site_id`) VALUES (2, 2), (3, 4), (6, 6), (7, 8); -- -------------------------------------------------------- -- -- Table structure for table `people_have_devices` -- CREATE TABLE IF NOT EXISTS `people_have_devices` ( `person_id` int(11) NOT NULL, `device_id` int(11) NOT NULL, PRIMARY KEY (`person_id`,`device_id`), KEY `device_id` (`device_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `people_have_devices` -- INSERT INTO `people_have_devices` (`person_id`, `device_id`) VALUES (1, 1), (2, 2), (3, 3); -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE IF NOT EXISTS `person` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; -- -- Dumping data for table `person` -- INSERT INTO `person` (`id`) VALUES (1), (2), (3), (4), (5), (6); -- -------------------------------------------------------- -- -- Table structure for table `site` -- CREATE TABLE IF NOT EXISTS `site` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ; -- -- Dumping data for table `site` -- INSERT INTO `site` (`id`) VALUES (1), (2), (3), (4), (5), (6), (7), (8); -- -------------------------------------------------------- -- -- Table structure for table `sites_have_devices` -- CREATE TABLE IF NOT EXISTS `sites_have_devices` ( `site_id` int(11) NOT NULL, `device_id` int(11) NOT NULL, PRIMARY KEY (`site_id`,`device_id`), KEY `device_id` (`device_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `sites_have_devices` -- INSERT INTO `sites_have_devices` (`site_id`, `device_id`) VALUES (3, 4), (4, 5); -- -------------------------------------------------------- -- -- Table structure for table `sites_have_people` -- CREATE TABLE IF NOT EXISTS `sites_have_people` ( `site_id` int(11) NOT NULL, `person_id` int(11) NOT NULL, PRIMARY KEY (`site_id`,`person_id`), KEY `person_id` (`person_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `sites_have_people` -- INSERT INTO `sites_have_people` (`site_id`, `person_id`) VALUES (1, 1), (2, 3), (5, 4), (6, 6); -- -- Constraints for dumped tables -- -- -- Constraints for table `groups_have_devices` -- ALTER TABLE `groups_have_devices` ADD CONSTRAINT `groups_have_devices_ibfk_2` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `groups_have_devices_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `groups_have_people` -- ALTER TABLE `groups_have_people` ADD CONSTRAINT `groups_have_people_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `groups_have_people_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `groups_have_sites` -- ALTER TABLE `groups_have_sites` ADD CONSTRAINT `groups_have_sites_ibfk_2` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `groups_have_sites_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `people_have_devices` -- ALTER TABLE `people_have_devices` ADD CONSTRAINT `people_have_devices_ibfk_2` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `people_have_devices_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `sites_have_devices` -- ALTER TABLE `sites_have_devices` ADD CONSTRAINT `sites_have_devices_ibfk_2` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `sites_have_devices_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; -- -- Constraints for table `sites_have_people` -- ALTER TABLE `sites_have_people` ADD CONSTRAINT `sites_have_people_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `sites_have_people_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; SET FOREIGN_KEY_CHECKS=1; /*!40101 SET CHARACTER_SET_CLIENT=@OLD _CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD _CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD _COLLATION_CONNECTION */;
Here is a query to find all the child devices of each group.
SELECT `group`.`id` AS `group_id`, `device`.`id` AS `device_id` FROM `group` INNER JOIN groups_have_devices ON (group.id=groups_have_devices.group_id) INNER JOIN device ON (groups_have_devices.device_id=device.id) UNION ALL SELECT `group`.`id` AS `group_id`, `device`.`id` AS `device_id` FROM `group` INNER JOIN groups_have_people ON (group.id=groups_have_people.group_id) INNER JOIN person ON (groups_have_people.person_id=person.id) INNER JOIN people_have_devices ON (person.id=people_have_devices.person_id) INNER JOIN device ON (people_have_devices.device_id=device.id) UNION ALL SELECT `group`.`id` AS `group_id`, `device`.`id` AS `device_id` FROM `group` INNER JOIN groups_have_sites ON (group.id=groups_have_sites.group_id) INNER JOIN site ON (groups_have_sites.site_id=site.id) INNER JOIN sites_have_devices ON (site.id=sites_have_devices.site_id) INNER JOIN device ON (sites_have_devices.device_id=device.id) UNION ALL SELECT `group`.`id` AS `group_id`, `device`.`id` AS `device_id` FROM `group` INNER JOIN groups_have_sites ON (group.id=groups_have_sites.group_id) INNER JOIN site ON (groups_have_sites.site_id=site.id) INNER JOIN sites_have_people ON (site.id=sites_have_people.site_id) INNER JOIN person ON (sites_have_people.person_id=person.id) INNER JOIN people_have_devices ON (person.id=people_have_devices.person_id) INNER JOIN device ON (people_have_devices.device_id=device.id) ORDER BY group_id
And here is a request to get all devices and their direct parent.
SELECT device.id AS device_id, person.id AS person_id, NULL AS site_id, NULL AS group_id FROM device INNER JOIN people_have_devices ON (device.id=people_have_devices.device_id) INNER JOIN person ON (people_have_devices.person_id=person.id) UNION ALL SELECT device.id AS device_id, NULL AS person_id, site.id AS site_id, NULL AS group_id FROM device INNER JOIN sites_have_devices ON (device.id=sites_have_devices.device_id) INNER JOIN site ON (sites_have_devices.site_id=site.id) UNION ALL SELECT device.id AS device_id, NULL AS person_id, NULL AS site_id, group.id AS group_id FROM device INNER JOIN groups_have_devices ON (device.id=groups_have_devices.device_id) INNER JOIN `group` ON (groups_have_devices.group_id=group.id)
You can also get direct child devices for a specific person, group, or site, for example
SELECT device_id FROM ( SELECT device.id AS device_id, NULL AS person_id, site.id AS site_id, NULL AS group_id FROM device INNER JOIN sites_have_devices ON (device.id=sites_have_devices.device_id) INNER JOIN site ON (sites_have_devices.site_id=site.id) ) sub_query WHERE sub_query.site_id='3'