Relationship of Tables - how to do

Question

I’m having trouble figuring out how to relate to tables. My scenario is below:

A parceiro may have several usuários, however a usuário may only be attached to one parceiro. A usuário can register numerous pedidos, and these pedidos will be hitched usuário who registered and parceiro user. Example: pedido to is attached to the usuário cesar in which this usuário is attached to the parceiro partner x.

To make a new pedido the code (id) of the parceiro and the details of the new application, such as: name, rg, Cpf and email

In this pedido there’s a chave de vpn in which it is unique and being formed by partner-Cpf. In a table vpnCode has the chave de vpn, bytes received, sent, server ip, real ip.

One VPN has chave de vpn, the parceiro that generated it, the cpf of pedido, the rg of pedido.

What I can’t understand (I’ve been trying for over a week to figure out how to do it) is how to make these relationships. Please, I need help.

Answer 1

I think this architecture should solve your problem. I didn’t put all the VPN fields but you can do it yourself.

If you need the code that generates these tables, see below:

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`PARCEIRO`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PARCEIRO` (
  `pk_parceiro` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NULL,
  `cpf` VARCHAR(45) NULL,
  `RG` VARCHAR(45) NULL,
  `E-mail` VARCHAR(45) NULL,
  `outrosCampos` VARCHAR(45) NULL,
  PRIMARY KEY (`pk_parceiro`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`USER`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`USER` (
  `pk_user` INT NOT NULL AUTO_INCREMENT,
  `pk_father` INT NULL,
  `Name` VARCHAR(45) NULL,
  `outrosCampos` VARCHAR(45) NULL,
  PRIMARY KEY (`pk_user`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`USER_PARCEIROS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`USER_PARCEIROS` (
  `fk_user` INT NOT NULL,
  `fk_parceiro` INT NOT NULL,
  `outrosCampos` VARCHAR(45) NULL,
  PRIMARY KEY (`fk_user`, `fk_parceiro`),
  INDEX `fkp_idx` (`fk_parceiro` ASC),
  CONSTRAINT `fku`
    FOREIGN KEY (`fk_user`)
    REFERENCES `mydb`.`USER` (`pk_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkp`
    FOREIGN KEY (`fk_parceiro`)
    REFERENCES `mydb`.`PARCEIRO` (`pk_parceiro`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`vpnCode_Parc`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`vpnCode_Parc` (
  `pk_vpnCOde` INT NOT NULL AUTO_INCREMENT,
  `vpnCode` VARCHAR(450) NOT NULL,
  `fkParceiro` INT NOT NULL,
  `outrosCampos` VARCHAR(45) NULL,
  PRIMARY KEY (`pk_vpnCOde`),
  INDEX `fkkpar_idx` (`fkParceiro` ASC),
  CONSTRAINT `fkkpar`
    FOREIGN KEY (`fkParceiro`)
    REFERENCES `mydb`.`PARCEIRO` (`pk_parceiro`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

---

If a user can only have one partner and this can be associated with multiple users then the model looks like this: