Schema one to many designing in MySQL

[garcon1986]

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 several experiences and a consultant can have several experiences. But salarie and consultant are different functions. I don't think i should combine the two tables.

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 about the following designing?

CREATE TABLE IF NOT EXISTS consultant{
    c_id int(10) NOT NULL AUTO_INCREMENT,
    post varchar(255),
    entreprise_name varchar(255),
    start_time varchar(255),
    end_time varchar(255),
    description varchar(10000),
    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.

[apegram]

You are going down the right path with your alternate idea. If a consultant can have multiple experiences and an experience can have multiple consultants, then you have a many-to-many relationship between consultants and experiences, and you would express that relationship in the form of a matching table. The same would be true if your "salarie" table has the same many-to-many relationship with experience.

[garcon1986]

You are going down the right path with your alternate idea. If a consultant can have multiple experiences and an experience can have multiple consultants, then you have a many-to-many relationship between consultants and experiences, and you would express that relationship in the form of a matching table. The same would be true if your "salarie" table has the same many-to-many relationship with experience.

Thanks, in the context, an experience only belongs to one consultant or salarie.

So, am i right ?

[apegram]

Thanks, in the context, an experience only belongs to one consultant or salarie.

So, am i right ?

If the experience only belongs to one "consultant" and one "salarie", then it sounds like the many-to-many relationship is between consultants and salaries and the experience table is the matching table between the two, meaning you should leave well enough alone. There is no need to split the table design further.

Or is it the case that experience as it relates to consultants is completely, wholly unrelated to experience as it relates to salaries? Meaning they might as well be different entities altogether? If so, then you should split the experience table into two seperate tables, one for consultants and one for salaries.

It really comes down to what your business rules happen to be.