Tables with a single parent in one of n other tables

Question

Assume I have a schema like:

group
-----
id

site
----
id
group_id (optional)

person
------
id
group_id (one of these two must exist 
site_id   and the other must be null)

device
------
id 
group_id  (one of these three must exist 
site_id    and the others must be null)
person_id  

I don't like this representation, but I'm struggling to find a better one.

The two alternatives I've thought of are:

device
------
id 
parent_table_name
parent_id

(but this is bad because I can't have foreign keys any more)

and:

entity
------
id

group
-----
entity_id

site
----
entity_id
link_entity_id (optional)

person
------
entity_id
link_entity_id (optional)

device
------
entity_id
link_entity_id

This is also less than perfect. It's really the Django ORMs method of inheritance, where entity is the parent of all the other classes.

Is there a better way of structuring the data, or is SQL just at odds with DAGs?

Is there a way of adding CONSTRAINTs to the person and device tables?

Answer 1

The following MySQL structure should normalize just fine. It will make your queries a little more complicated to write for some occasions, but it will make the application more powerful and able to grow exponentially without taking a hit on performance. We have a large MySQL database with many relating tables that hold foreign keys for people to various interviews, notes, and other data that works terrific! One note is that if you use group as a table name remember to use `` marks like:

`group`

That way MySQL does not try to invalidate a INNER JOIN group ON (foo=bar) and expect GROUP BY. You will also have to put restraints in the front end of your application that would prevent a device being added without a parent if that is the desired goal. But that is not too hard to do. Anyways look at the examples and have fun experimenting/programming!

Online Demo: http://www.sqlfiddle.com/#!2/e9e94/2/0

Here is the proposed MySQL table structure with the smallest amount of data to account for one instance of each needed case from your question: Copy and Paste into .sql file and import to 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 child devices of every 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 query 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 further get the devices that are direct children for a particular person, group, or site like this

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'

Answer 2

This is a typical type/subtype situation. Your second option is better and you could take it one step further. Think in terms of OO programming if you are more familiar with these concepts.

This is the how I would classify your entities. "Abstract" entities are in brackets.

             (Owner)                 Device
               |
         +-----------+
         |           |
   (Afffiliation)  Person
         |
     +-------+
     |       |
   Group    Site

Heres is how to read it:

• Types of owners are: a Person, or an Affiliation (I can't find a better name, sorry). A Person "is a" Owner, and an Affiliation "is a" owner.
• Types of Affiliations are: a Group, or a Site
• A Person is affiliated with an Affiliation, either a Group or a Site
• A Device has a Owner, either a Group, or a Site, or a Person

How to translate this into tables:

Now you could stick to your first option. MySQL does not support the CHECK() syntax for declaring arbitrary constraints, but the same effect can be achieved through the use of triggers, However, the syntax is cumbersome, and the performance is dubious.