Schema for many to many in MySQL?

Question

Hello,

Now i have 3 tables which are:

CREATE TABLE IF NOT EXISTS experience(
   experience_id int(11) NOT NULL AUTO_INCREMENT,
   salarie_id int(11),
   consultant_id int(11),
   post varchar(255),
   entreprise_name varchar(255),
   start_time varchar(255),
   end_time varchar(255),
   description varchar(10000),
   PRIMARY KEY(experience_id)
 );
CREATE TABLE IF NOT EXISTS salarie(
   salarie_id int(11) NOT NULL AUTO_INCREMENT,
   name varchar(255),
   PRIMARY KEY(salarie_id)
 );
CREATE TABLE  IF NOT EXISTS consultant(
   consultant_id int(11) NOT NULL AUTO_INCREMENT,
   nom varchar(255),
   PRIMARY KEY(consultant_id)
 );

The context: A salarie can have different experiences and a consultant can have different experiences. But salarie and consultant are different roles.

I think i should modify experience table because it has two columns salarie_id int(11), consultant_id int(11).

What should i do?

Edit:

I think it's one-to-many relationship. Because one consultant/salarie can have as many as experiences, and one experience only belongs to one consultant/salarie. Am I right?

But I think the table experience shouldn't contain columns salarie_id int(11) and consultant_id int(11), at the same time.

What do you think?

And if it's a many-to-many relationship. What about the breif following designing?

CREATE TABLE IF NOT EXISTS consultant{
    c_id int(10) NOT NULL AUTO_INCREMENT,
    primary key(c_id)
}

CREATE TABLE IF NOT EXISTS salarie{
    s_id int(10) NOT NULL AUTO_INCREMENT,
    primary key(s_id)
}    
 CREATE TABLE IF NOT EXISTS experience{ 
    e_id int(10) NOT NULL AUTO_INCREMENT,
    primary key(e_id)
}

CREATE TABLE IF NOT EXISTS  salarie_experience{
    se_id int(10) NOT NULL AUTO_INCREMENT,
    s_id int(10),
    e_id int(10),
    primary key(se_id),
    foreign key(s_id) references salarie(s_id) on delete cascade,
    foreign key(e_id) references experience(e_id) on delete cascade
}    
CREATE TABLE IF NOT EXISTS consultant_experience
    ce_id NOT NULL AUTO_INCREMENT,
    c_id int(10),
    e_id int(10),
    primary key(ce_id),
    foreign key(c_id) references consultant(c_id) on delete cascade,
    foreign key(e_id) references experience(e_id) on delete cascade
} 

Thanks a lot.

Answer 1

if i understood correctly, this is an M:M relation ( http://en.wikipedia.org/wiki/Many-to-many_%28data_model%29 ) and you need a junction table or tables, like consultant_experience(consultant_id, experience_id)

Answer 2

As I understand it, you want salarie and consultant to be able to have many experiences but one particular experience should not belong to several different consultants/salaries. Therefore, I think you designed it correctly but perhaps you should set foreign key constraints on salarie_id and consultant_id in the experience table.

However, it might be so that you rather have employees which in turn have roles. For example:

-- -----------------------------------------------------
-- Table employe
-- -----------------------------------------------------
DROP TABLE IF EXISTS employe ;

CREATE  TABLE IF NOT EXISTS employe (
  idemploye INT NOT NULL AUTO_INCREMENT ,
  nom VARCHAR(255) NOT NULL ,
  PRIMARY KEY (idemploye) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table experience
-- -----------------------------------------------------
DROP TABLE IF EXISTS experience ;

CREATE  TABLE IF NOT EXISTS experience (
  experience_id INT(11) NOT NULL AUTO_INCREMENT ,
  employe_idemploye INT NOT NULL ,
  post VARCHAR(255) ,
  entreprise_name VARCHAR(255) ,
  start_time VARCHAR(255) ,
  end_time VARCHAR(255) ,
  description VARCHAR(10000) ,
  PRIMARY KEY (experience_id, employe_idemploye) ,
  INDEX fk_experience_employe (employe_idemploye ASC) ,
  CONSTRAINT fk_experience_employe
    FOREIGN KEY (employe_idemploye )
    REFERENCES employe (idemploye ));


-- -----------------------------------------------------
-- Table role
-- -----------------------------------------------------
DROP TABLE IF EXISTS role ;

CREATE  TABLE IF NOT EXISTS role (
  idrole INT NOT NULL AUTO_INCREMENT ,
  employe_idemploye INT NOT NULL ,
  nom VARCHAR(45) NOT NULL ,
  PRIMARY KEY (idrole, employe_idemploye) ,
  INDEX fk_role_employe (employe_idemploye ASC) ,
  CONSTRAINT fk_role_employe
    FOREIGN KEY (employe_idemploye )
    REFERENCES employe (idemploye ))
ENGINE = InnoDB;