Should create a surrogate key instead of a composite key?

Question

Composition:

Actor <=== ActorMovie ===> Movie

ActorMovie: ActorID (fk), MovieId (fk)... ===> pk: (ActorID, MovieID)

Should I create a surrogate key for a table ActorMovielike this?

ActorMovie: ActorMovieID (pk), ActorID (fk), MovieId (fk)...

Answer 1

Conventions are good if they are useful

"SQL Antipatterns", Chapter 4, "ID Required"

Primary key intent

The primary key is what you can use to identify your row with a unique address in the table. This means that the primary key can be not only a surrogate column. In fact, the primary key should be:

• . . , , .
• . , (.. , )
• Single. , .

, . - . first_name + last_name + taxpayer_id ? - . , , , . , , .

, "--" , , . , , , ( - , , ). ActorID + MovieID , . , ( ), , . , , :

• . , . : ,
• (, , ) . .

- , , ( ) , antipattern - .

:

• SQL
• SQL Antipatterns

Answer 2

. :

• , , .
• / , .
• .

, , .

Answer 3

, , -, : InnoDB .

, ​​ B-Tree, . B-Tree ( " ", , - , ), .

, .

Answer 4

, () (-), ( ), . , , .

Answer 5

(1NF) .

ActorID MovieID , ActorMovieID.

. :

Answer 6

: , , .

, " , ", . . . .

Answer 7

:

• .
• . , ; , .
• , ( , , , ). , , .
• , , , , , .
• - , CRUD , .
• . , . , , , .
• , -, , .
• .
• , , . , , . , .