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:
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.
users
andgroups
is the "user owns group" relation. If I don't read the diagram wrong you say that a user has to own at least one group. Is this your intention? You say nothing about events and profiles so one cannot say anything about their diagrams.groups
andevents
you haveFOREIGN KEY (id) REFERENCES mydb.users (id)
. It should beFOREIGN KEY (user_id) REFERENCES mydb.users (id)
in groups (and probably inevents
as well. Do events have an owner, like groups?)