jacek,
There are
never 2 PK's in a table.
There may be 2 or more fields used to make a compound primary key -and I think that is where you are confused. It is still a single primary key that just happens to be made up of multiple fields.
A Primary key is a field or fields that uniquely identify each and every record in that table.
Normalization rules would tell you to remove repeating groups.
So your
So having repeatedly pairs (when your business wants to) it is not a correct approach?
would say that such repeating pairs would represent a non-normalized structure.
Bottom line:
A Primary key can be composed of more than 1 field. Every table should have a Primary key.
A Primary Key may be a single field, or may be composed of multiple fields (in Access I believe the maximum is 10 fields). However, whether a single field or multiple fields it still represents a single Primary Key.
As Doc pointed out, if you have a junction table to "represent a M:M relationship", you can
-use a compound PK made up of the PK of each of the tables requiring the junction construct OR
-use an autonumber single field (surrogate) PK, and if you do,it is recommended to use a composite unique index to prevent duplicates.
If you use the compound PK (made up of the PK of each of the tables requiring the junction construct), you may find the structure becomes confusing/awkward if that junction table is involved in other junction tables. Many Access developers use a single field autonumber PK for every table --this is not a necessary rule, but it is a consistent technique for those who use it.
Also, a comment on some other things I notice in your posts. A data model with tables and relationships is a reflection or implementation of your business rules. In my view, you can not have arbitrary tables and relationships. You start with a problem/issue/opportunity and identify the requirements and the rules within the "business".
Any way good luck with your project.