A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearHow to design MySQL Database - page 1
User InfoPosts
How to design MySQL Database#1
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??


posted date: 2009-04-11 08:22:00

Re: How to design MySQL Database#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-11 08:22:01

Re: How to design MySQL Database#3
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.

posted date: 2009-04-11 08:24:00

Re: How to design MySQL Database#4
Male all three columns as primary key.

posted date: 2009-04-11 08:25:00

Re: How to design MySQL Database#5
It doesn't affect performance to have 3 columns with lot of info (role) as primary keys??

posted date: 2009-04-11 08:28:00

Re: How to design MySQL Database#6
Just what are you storing in role? What is its datatype?

posted date: 2009-04-11 08:29:00

Re: How to design MySQL Database#7
If role is a large column, use an artificial key instead. It need only be an auto-incremented integer. Then you can define a constraint on the remaining columns if still need uniqueness.

posted date: 2009-04-11 08:30:00

Re: How to design MySQL Database#8
role is VARCHAR 80, but I need movieID and personID to be primary so I can refer to them from another tables..

posted date: 2009-04-11 08:32:00

Re: How to design MySQL Database#9
You can always refer to things even if they aren't primary, though without an index there would be a performance hit. I don't know how MySQL handles strings as primary keys, so I can't tell you which way is better. Can you put roles in another table and store references to that instead of strings?

posted date: 2009-04-11 08:36:00

Re: How to design MySQL Database#10
I can put roles in another table but I will be using to many tables for a too simple thing, so I will go with the 3 primary keys.. Thanks!!

posted date: 2009-04-11 08:38:00

Re: How to design MySQL Database#11
I am suggesting some changes to your design: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.This table should have its own primary key called movieperson_xrefID.You can then save all combinations of movie ID(s and person ID(s.

posted date: 2009-04-11 08:46:00

Re: How to design MySQL Database#12
Based on some comments you(ve made, I think you need more normalization. Create a role table and adjust your lookup table accordinglymovie+----+------------+| 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.

posted date: 2009-04-11 08:47:00

select page: « 1 2 »
Copyright ©2008-2017 www.momige.com, all rights reserved.