MySQL. Primary key in a relational table. Unique i

Question

Primary key in relational tables. Composite primary key or unique primary key in those pure relational tables?

Which design would you recommend to use in MySQL for high performance? See diagram

Technical advantages and disadvantages!

Thanks everyone!

Answer 1

According to Elmasri and Navathe (in Fundamentals of Database Systems) you should choose option A because artificial primary keys are unnecessary and suggest that you have a denormalized design (their POV).

In the case of a join table, I happen to agree with the authors. Otherwise I think that in practice using artificial keys is perfectly fine.

I don't think the performance implications are tremendous on a join table.

Answer 2

I agree with @Denis, it'll depend on what you're doing. One other thing to consider is that the InnoDB will store the rows in PK order on disk. This is very important if you're doing things like id1 BETWEEN a AND b. Moving those read heads is about 10ms each time, and if the rows for your query are scattered, it'll add up. For exactly these reasons, you might consider denormalizing to putting the data you need in a single row.

Answer 3

It really depends on the type of query you're doing...

If you add an extra surrogate, you'll end up doing two unique checks instead of a single one for every insert, update and delete. That makes the composite key sound right.

But if you're constantly referencing that primary key in other foreign keys, the composite option means you end up storing twice as much information and need to do more work when joining. That makes the single key sound right.