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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
