Mysql – is this database structure correct

database-designMySQL

I need some help with a new database structure because I want to make it right.
Later in the app you should be able to create users, events and groups.

When you create a group you can add members (users) to it.
When the group owner deletes the group, all references should be deleted.

A user can be the owner of multiple groups and can be a member of multiple groups.

When a user is deleted, the groups he owns and all references should be deleted.

I created a diagram to show this but I´m not sure if it is correct:

EER diagram

MySQL:

-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
  `id` INT NOT NULL,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `firstname` VARCHAR(45) NULL,
  `lastname` VARCHAR(45) NULL,
  `gender` VARCHAR(45) NULL,
  `created_on` VARCHAR(45) NULL,
  PRIMARY KEY (`id`, `username`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`groups` (
  `id` INT NOT NULL,
  `group_name` VARCHAR(45) NULL,
  `is_public` VARCHAR(45) NULL,
  `sport_type` VARCHAR(45) NULL,
  `date` VARCHAR(45) NULL,
  `user_id` INT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_id`
    FOREIGN KEY (`id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`events`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`events` (
  `id` INT NOT NULL,
  `event_name` VARCHAR(45) NULL,
  `location_name` VARCHAR(45) NULL,
  `street` VARCHAR(45) NULL,
  `street_nr` VARCHAR(45) NULL,
  `plz` VARCHAR(45) NULL,
  `town` VARCHAR(45) NULL,
  `tel` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `date_from` VARCHAR(45) NULL,
  `date_to` VARCHAR(45) NULL,
  `geo` GEOMETRY NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_id`
    FOREIGN KEY (`id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`userEventsLink`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`userEventsLink` (
  `id` INT NOT NULL,
  `user_id` INT NULL,
  `event_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `event_id_idx` (`event_id` ASC),
  INDEX `user_id_idx` (`user_id` ASC),
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `event_id`
    FOREIGN KEY (`event_id`)
    REFERENCES `mydb`.`events` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`userGroupLink`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`userGroupLink` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `group_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `group_id_idx` (`group_id` ASC),
  INDEX `user_id_idx` (`user_id` ASC),
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `group_id`
    FOREIGN KEY (`group_id`)
    REFERENCES `mydb`.`groups` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`profiles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`profiles` (
  `id` INT NOT NULL,
  `trikot_nr` VARCHAR(45) NULL,
  `handy_nr` VARCHAR(45) NULL,
  `spieler_nr` VARCHAR(45) NULL,
  `user_id` INT NULL,
  INDEX `user_id_idx` (`id` ASC),
  CONSTRAINT `user_id`
    FOREIGN KEY (`id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Let´s take the example that the user creates a group and adds members to it.
The table groups has a foreign key to users.user_id.
Then he adds a user and this information will go in the table userGroupLink.

The table userGroupLink has two foreign keys:

group_id -> groups.id

user_id -> users.id

When the user is a member of multiple groups then he would have multiple entries in this table with his user_id and different group_id´s.

I have two questions now:
Is this structure good or are there better solutions?

And how would the MySQL query look like when I want to get all groups where a user is a member of?

Edit: The field types and length are not final yet.

Best Answer

For the Users, Groups and userGroupLink tables you have the relationships correct.

I would suggest you change groups.user_id to owner_user_id just to make it a little bit clearer, but that's a minor point of preference. Since userGroupLink has no attributes of its own nor is it the parent in any relationships you could remove the ID column from this table without any loss of meaning or utility. Indeed you will get a small performance boost by storing more rows per page.

To find all the groups of which a user is a member you would join the tables together:

select
    u.id
    ,u.username
    ,g.group_name
from mydb.users as u
inner join  mydb.userGroupLink as l
    on l.user_id = u.id
inner join mydb.groups as g
    on g.id = l.group_id
where u.id = <the user id you are looking for>

If you are looking for the groups which a user owns that would follow the other relationship.

Related Question