Indexing a Many to Many Table

ozzii · #1 (**permalink**) 04-23-07, 12:03

Hi,

I have many to many table called Jobseeker_IndustryExp. It has the following fields:

recordid - this auto increment number
jobseekerid - this foriegn key from jobseeker table
industryid this is foriegn key from industry table

Would you recomend indexing the jobseekerid and industryid fields in the above table - a frequent Select query will be used to do an inner join on the above table to search for jobseekers who match certain industry experience?

aschk · #2 (**permalink**) 04-23-07, 12:28

Why do you have an auto_increment field in there? Surely because this is a mapping table you don't need that field. And i'm guessing (if you've designed your DB correctly) that the jobseekerid and industryid combination ought to be unique.

Personally I would do the following :
1) Remove the auto_increment
2) Create the primary key on (jobseekerid,industryid)

ozzii · #3 (**permalink**) 04-23-07, 14:43

Yep u right. both jobseekerid and industryid are unique primary keys of their respected tables. The recordid i just created - no particular reason! Whats the syntax for defining a compound primary key though?

r937 · #4 (**permalink**) 04-23-07, 16:27

Quote:

Originally Posted by ozzii

Whats the syntax for defining a compound primary key though?

Code:

create table Jobseeker_IndustryExp
( jobseekerid integer not null 
, industryid  integer not null 
, primary key (jobseekerid, industryid)
, constraint job_fk
     foreign key (jobseekerid) references jobseeker
, constraint industry_fk
     foreign key (industryid) references industry 
);

say, ozzii, would you mind answering my question in this other thread?

Internet Services

Web Development

Digital Marketing

Consumer Tech