CODEWITHSUNDEEP
phpmysqldatabasedatabase-design
jonagoldman
jonagoldman

Reputation: 8754

How to design MySQL Database

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 can I do it??

Thanks..

Upvotes: 0

Views: 316

Answers (5)

Peter Bailey
Peter Bailey

Reputation: 105914

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.

Upvotes: 1

Srikar Doddi
Srikar Doddi

Reputation: 15609

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.

Upvotes: 0

Glavić
Glavić

Reputation: 43582

Male all three columns as primary key.

Upvotes: 0

Seb
Seb

Reputation: 25157

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.

Upvotes: 0

Welbog
Welbog

Reputation: 60458

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.

Upvotes: 2

Related Questions