0

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.

  • I found a short description of the Crow's Foot notation here but no explanation for dashed lines. What is the meaning of the dashed lines? It would be useful if the relations have names. Is it possible to name them? I think the relation between usersand groups 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. – miracle173 May 12 '14 at 6:24
  • Ok, I added the MySQL code. The dotted lines are foreign keys and the normal lines are just for me and just say that there is a 1:n connection. A 1:1 connection would be just a line without the symbols at the ends. All indexes with idx at the end are foreign keys. Hope it´s clearer now... – Nico May 12 '14 at 10:33
  • In groups and events you have FOREIGN KEY (id) REFERENCES mydb.users (id). It should be FOREIGN KEY (user_id) REFERENCES mydb.users (id) in groups (and probably in events as well. Do events have an owner, like groups?) – ypercubeᵀᴹ May 12 '14 at 13:10
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.