DB design to hold complex attribute
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 (`g开发者_运维技巧roup_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 :(
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.
精彩评论