Nesting of groups or elements

I'm fairly new in Mysql, but I have problem that I cannot solve. I will give you an example to demonstrate it. Please note that I know that (for current example) there are other simpler and more efficient ways to solve it... but just take it as an example of the required procedure.

• First the data: The data would be the name of a Person.

CREATE TABLE person(
          id INT,
          name VARCHAR(100)
        ) TYPE=innodb;

• Second: Group Creation... So this is fairly simple... and could easily done using a table 'group' with a foreignkey to person. These groups could be arbitrary, containing any number of persons, duplicated... or not... (that is simple!!)
• Third: MY REAL PROBLEM--- I also would like to have Groups that have other Groups as elements (instead of persons). This is where a really get stuck, because I know how to create a groups of persons, a group of groups (having a self-referencing foreign key)... but I don't know how to cr开发者_如何学Goeate a group that MAY HAVE persons AND Groups.

I appreciate any suggestion to solve this issue.

Thank you very much for your comments. Regards

ACombo

I'd go with firstly setting up the myGroup and person tables.

Secondly, I'd set up a myGroupGroup table with columns myGroupId, parentMyGroupId. This will allow you to relate group rows to child group rows i.e. "this group has these groups within it". If a group has no rows in this table then it has no child groups within it.

Thirdly, I'd set up a personGroup table with columns personId, myGroupId. This will allow you to relate person rows to a given group. If a group has no rows in this table then it has no persons within it.

CREATE TABLE person(
      id INT UNSIGNED PRIMARY KEY,
      name VARCHAR(100)
    ) ENGINE=innodb;


CREATE TABLE myGroup(
      id INT UNSIGNED PRIMARY KEY,
      groupName VARCHAR(100)
    ) ENGINE=innodb;

-- Holds groups within groups
CREATE TABLE myGroupGroup(
      id INT UNSIGNED PRIMARY KEY,
      myGroupId INT UNSIGNED,
      parentMyGroupId INT UNSIGNED DEFAULT NULL,
      CONSTRAINT `fk_myGroupGroup_group1` FOREIGN KEY (`parentMyGroupId`) REFERENCES `myGroup` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `fk_myGroupGroup_group2` FOREIGN KEY (`myGroupId`) REFERENCES `myGroup` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=innodb;

-- Holds persons within a group
CREATE TABLE personGroup(
      id INT,
      personId int UNSIGNED NOT NULL,
      myGroupId int UNSIGNED NOT NULL,
      CONSTRAINT `fk_personGroup_group1` FOREIGN KEY (`myGroupId`) REFERENCES `myGroup` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `fk_personGroup_person1` FOREIGN KEY (`personId`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=innodb;

I've tweaked your SQL a bit:

1) Replaced TYPE with ENGINE

2) Replaced table name group with myGroup (GROUP is a reserved word)

Good luck!

Alternative:

CREATE TABLE Entity
        ( EntityId INT                   --- this id could be AUTO_INCREMENT
        , PRIMARY KEY (EntityId)
        ) ENGINE = InnoDB ;

CREATE TABLE Person
        ( PersonId INT                   --- but not this id
        , PersonName VARCHAR(100)
        , PRIMARY KEY (PersonId)
        , FOREIGN KEY (PersonId) 
            REFERENCES Entity(EntityId)        
        ) ENGINE = InnoDB ;

CREATE TABLE Grouping
        ( GroupingId INT                 --- and neither this id
        , GroupingName VARCHAR(100)
        , PRIMARY KEY (GroupingId)
        , FOREIGN KEY (GroupingId) 
            REFERENCES Entity(EntityId)            
        ) ENGINE = InnoDB ;

CREATE TABLE Belongs
        ( EntityId INT
        , GroupingID INT
        , PRIMARY KEY (EntityId, GroupingId)
        , FOREIGN KEY (EntityId) 
            REFERENCES Entity(EntityId) 
        , FOREIGN KEY (GroupingID) 
            REFERENCES Grouping(GroupingId)            
        ) ENGINE = InnoDB ;