I have 2 table with many - many relationships. So I use the third table to mapping 2 table together. And I don't know what should I choose between having an auto-incremented, integer primary key then put unique indexes on the others or use a primary key have many values. What are benefits of each way?
Thank a lot
In theory of Data Modeling, both solutions are correct.
But in practice: It's better to use second solution.
Taking new Auto-Increment ID and set it as Primary-Key and set two transmitted foreign keys as Unique (together).
Advantages:
Redundancy of Data:
Assume that we have two tables named A and B and AB is new created middle table (because of many-to-many relationship).
Now if AB has a new relationship (one-to-many) with C. We should transmit Primary-Key of AB as foreign key to C. So it's better to transform ID instead of two attributes. As the same way if C has a new relationship (one-to-many) with D ... and so on.
Disadvantages:
Access Performance to IDs: Although there is redundancy in first solution, but there is a performance to access IDs without using any JOINs. (Assume that in table C, we want to access of A and B IDs.) But, accessing only to IDs is not much used.