MySQL: Database Design problem

Question

I have a table called 'movie2person' with 3 columns: movieID, personID and role. I use this table to connect betwen the 'movies' table and the 'persons' table... many-to-many relationship..

I have selected movieID and personID both as primary keys... The problem is that sometimes I need to enter the same personID for the same movieID several times and I can't do it because only one combination of the same movieID and personID is permited...

How I can I do it??

Thanks..

Answer 1

Either include role in the primary key, or add a new artificial key to the table and use this new column as the primary key, which you wouldn't use outside of this table.

Answer 2

Primary keys are meant to mean "This is the unique identifier of this row".

If you're inserting many rows with the same values, then that's not your primary key.

If you plan to insert exact duplicates for rows, then you don't have a primary key at all and you should drop it for good.

If, however, you plan to insert different roles to each (movieID, personID) pair, then you could just add the role to the primary key and you're good to go.

Answer 3

Male all three columns as primary key.

Answer 4

I am suggesting some changes to your design:

1. change the name of your table from Movie2Person to MoviePerson_xref. It is usually standard to name in sucha format and exlude numbers from your table naming conventions.

2. This table should have its own primary key called movieperson_xrefID.

3. You can then save all combinations of movie ID's and person ID's.

Answer 5

Based on some comments you've made, I think you need more normalization. Create a role table and adjust your lookup table accordingly

movie
+----+------------+
| id | title      |
+----+------------+
|  1 | Braveheart |
+----+------------+

person
+----+------------+
| id | name       |
+----+------------+
|  4 | Mel Gibson |
+----+------------+

role
+----+------------+
| id | title      |
+----+------------+
|  1 | Director   |
|  2 | Actor      |
+----+------------+

movie2person
+---------+----------+--------+
| movieID | personID | roleID |
+---------+----------+--------+
|       1 |        4 |      1 |
|       1 |        4 |      2 |
+---------+----------+--------+

With this setup you'd have a three-column composite primary key on movie2person.