Primary type in relational tables. Composite primary key or unique primary type in individuals pure relational tables?
Which design can you recommend to make use of in MySQL for top performance? See diagram
Technical pros and cons!
It truly is dependent on the kind of query you are doing...
Should you add an additional surrogate, you'll finish up doing two unique inspections rather than just a single one for each place, update and remove. Which makes the composite key seem right.
But when you are constantly referencing that primary type in other foreign secrets, the composite option means you finish up storing two times just as much information and have to do more work when joining. Which makes the only key seem right.
Based on Elmasri and Navathe (in Basic principles of Database Systems) you need to choose option A because artificial primary secrets are unnecessary and suggest that you've a denormalized design (their POV).
Within the situation of the join table, I occur to accept the authors. Otherwise I believe that used using artificial secrets is perfectly fine.
I do not think the performance implications are tremendous on the join table.
To be sure with @Denis, it'll rely on what you are doing. Another factor to think about would be that the InnoDB will keep rows in PK order on disk. This is important if you are doing such things as
id1 BETWEEN a AND b. Moving individuals read heads is all about 10ms every time, and when the rows for the query are scattered, it'll accumulate. For exactly these reasons, you may consider denormalizing to putting the information you'll need in one row.