DB design to hold complex attribute

Question

I'm developing a PHP web app that handles information about certain companies. So far, the DB design looks like this:

CREATE TABLE `group` (
  `group_id` int(10) unsigned NOT NULL auto_increment,
  `group_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`group_id`)
) ENGINE=InnoDB;

CREATE TABLE `company` (
  `company_id` int(10) unsigned NOT NULL auto_increment,
  `company_name` varchar(50) NOT NULL,
  `group_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`company_id`),
  KEY `FK_company_group` (`group_id`),
  CONSTRAINT `FK_company_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

The design is too simplistic and I need to alter it so it can hold the actual information about company classification. To sum up, a company can be in one (and only one) of four possible situations and can optionally be linked to another object (a group or another company); the type and characteristics of the linked object depend on the situation:

                -  Independent           
               /             
              /               
   - Holding /                    
  /          \                 --- Direct -> Group
 /            \               /               
/              \             /               
\               -  Dependent ----- Indirect -> Company of "Direct" type                     
 \                                     
  \                                    
   - Subsidiary -> Company of "Holding" type                                  

So a company can be "Independent", "Direct", "Indirect" or "Subsidiary". Arrows represent links. (Sorry if the names make little sense, I'm translating from Spanish on the fly.)

I'm looking for a DB design to hold this information and avoid incoherences as much as InnoDB allows (so you can't have a "subsidiary" linked to a group or a "direct" linked to a company), esp. when updates are done (you should not be able to move a holding company to subsidiary if it already has subsidiaries).

Can you give me some ideas? I'm going nowhere by myself :(

Answer 1

Enforcing data integrity in MySQL is impossible. MySQL silently discards check constraints. Triggers are not reliable (for example, a before insert trigger is not invoked for a cascading delete.) And even if they were reliable, MySQL triggers can't actually prevent a row from being inserted.

Your best hope is a layer on top of MySQL that enforces the business rules.