i used the latter!
Details
CREATE TABLE `ressources` (
`ressource_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`ressource_id`),
)
CREATE TABLE `ressgroups` (
`ressgroup_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`ressgroup_id`)
)
CREATE TABLE `applications` (
`app_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`app_id`)
)
Relations
Ressources : RessourceGroups m:n
RessourceGroups : Applications m:n
I did this:
CREATE TABLE `connections` (
`app_id` int(11) NOT NULL,
`ressgroup_id` int(11) NOT NULL,
`ressource_id` int(11) NOT NULL,
PRIMARY KEY (`app_id`,`ressource_id`,`ressgroup_id`),
KEY `fk_con_grp_idx` (`ressgroup_id`),
KEY `fk_con_ress_idx` (`ressource_id`),
CONSTRAINT `fk_con_grps` FOREIGN KEY (`ressgroup_id`) REFERENCES `ressgroups` (`ressgroup_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_con_apps` FOREIGN KEY (`app_id`) REFERENCES `applications` (`app_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_con_ress` FOREIGN KEY (`ressource_id`) REFERENCES `ressources` (`ressource_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
Only thing i'm not yet sure of:
There is also a relationship Ressources : Applications m:n
I implemented this by adding a row to
Ressgroups
with
name="NoGroup"
and adding all ressources that don't have a ressourcegroup to this one.