CODEWITHSUNDEEP
phpmysqlsql
PHPLovere
PHPLovere

Reputation: 11

MySQL normalization dilemma challenge

I am moving from an unnormalized table to normalized, but I got confused in the following situation:

MovieID(pk) | MovieTitle
--------------------- 
1           | Imagination
2           | Creativity
3           | Bodyguard


ActorID(pk) | Name
--------------------- 
25          | John
26          | Charlie
27          | George
28          | Lincoln

MovieID(ck) | ActorID(ck)     <---- Indexed Table
--------------------- 
1           | 25
1           | 26
2           | 25
2           | 28
3           | 26
3           | 27
3           | 28

Till here, everything should be fine, but then each actor has a role (or more) in the movie.

My solution is:

Adding the following table:

RoleID(pk)  | RoleTitle
--------------------- 
84          | Doctor
85          | Lawyer
86          | Farmer
87          | Judge
88          | Farmer John
89          | The Police
90          | Policeman
91          | Mother of Johny
92          | Prisoner
93          | Doctor

And modifying the indexed table to this:

MovieID(ck) | ActorID(ck) | RoleID(ck)     <---- Indexed Table
------------------------------------------ 
1           | 25          | 84
1           | 25          | 85
1           | 26          | 86
2           | 25          | 87
2           | 28          | 88
3           | 26          | 89
3           | 27          | 90
3           | 28          | 91
3           | 28          | 92
3           | 28          | 93

But then, what's the point of creating a new table, with values repeating, like in our case: RoleID: 84 and 93, and knowing that each row will be used only one time by one actor?

Can someone help me with this concern?

I can give more details, if it's not clear.

Upvotes: 1

Views: 40

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

A movie contains roles played by actors. So I see these tables (primary key is bold):

  • movie (movie_no, movie_title)
  • actor (actor_no, actor_name)
  • movie_role (movie_no, role_name, actor_no)

Upvotes: 0

Ruslan Dascal
Ruslan Dascal

Reputation: 41

The solution would be to remove Roles table, and add to the indexed table the roleTitle. To be able to add more role titles, this should be primary key with the others in the indexed table.

Upvotes: 1

Related Questions