I have 100K job posting records and 40K job title records. There is a M:M
relationship here. I expect these tables to grow rapidly.
What is the best way to design a junction or link table? Do I need to create
a primary key?
My thought was no primary key, just two indices on each foreign key (FK).
Some folks feel you should always have a primary key. However, if I make
fkJobTitle the primary key, can it still have duplicates?
Some folks say I should have a composite primary key consisting of
fkJobTitle and fkJobPosting. This does buy me uniqueness. However, what is
the performance like when I only know the fkJobTitle and I want to find all
job postings for that title. Is it a linear search? If not, why not? I don't
know the fkJobPosting to exploit the primary key.
Some folks say I say I should have a separate auto increment integer
separate from fkJobPosting and fkJobTitle. Then I could make the "id" field
(what is your favorite naming convention for this field?) the primary key
and index fkJobTitle and fkJobPosting separately. But this means every time
I insert into the junction table, I have to update three index structures.
Is this a problem?
What is your opinion?
Lastly, I have learned that MySQL has a rich set of extensions for the SQL
syntax. Let's suppose I have several hundred jobs which may or may not have
been previously inserted into my data base already where each job contains a
posting and a title. For each one I have to look it up, insert it if it does
not already exist in the database, and, return the integer PK.
So what SQL statements would I use to look up the job title, insert it if it
does not exist and return the integer PK. I could use SELECT and if that
fails, INSERT or vice versa. But a previous poster informed me there are
other statements like REPLACE or INSERT ... ON DUPLICATE... and maybe there
are others. (Apparently REPLACE will INSERT if it is not already there). I
was using REPLACE and now that I am using V5 I can use INSERT... ON
DUPLICATE. Which would be best?
Thanks,
Siegfried
