StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSchema for many to many in MySQL?
text
Body
copied!<p>Now i have 3 tables which are:</p> <pre><code>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) ); </code></pre> <p>The context: A <code>salarie</code> can have different <code>experience</code>s and a <code>consultant</code> can have different <code>experience</code>s. But <code>salarie</code> and <code>consultant</code> are different roles.</p> <p>I think i should modify <code>experience</code> table because it has two columns <code>salarie_id int(11), consultant_id int(11)</code>.</p> <p>What should i do? </p> <p>Edit: </p> <p>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? </p> <p>But I think the table experience shouldn't contain columns salarie_id int(11) and consultant_id int(11), at the same time. </p> <p>What do you think? </p> <p>And if it's a many-to-many relationship. What about the breif following designing? </p> <pre><code>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 } </code></pre> <p>Thanks a lot. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload