Edit Question - KnowledgeHub

Title Body

I am working on a couple of link tables and I got to thinking (Danger Will Robinson, Danger) what are the possible structures of a link table and what are their pro's and con's. I came up with a few possible strictures for the link table: Traditional 3 column model id - auto-numbered PRIMARY table1fk - foreign key table2fk - foreign key It's a classic, in most of the books, 'nuff said. Indexed 3 column model id - auto-numbered PRIMARY table1fk - foreign key INDEX ('table1fk') table2fk - foreign key INDEX ('table2fk') In my own experience, the fields that you are querying against are not indexed in the traditional model. I have found that indexing the foreign key fields does improve performance as would be expected. Not a major change but a nice optimizing tweak. Composite key 2 columns ADD PRIMARY KEY ('table1fk' , 'table2fk') table1fk - foreign key table2fk - foreign key With this I use a composite key so that a record from table1 can only be linked to a record on table2 once. Because the key is composite I can add records (1,1), (1,2), (2,2) without any duplication errors. Any potential problems with the composite key 2 columns option? Is there an indexing issue that this might cause? A performance hit? Anything that would disqualify this as a possible option?

Tags (comma-separated) Cancel